sql - Mysql, get rows before specific row in a multi-column index -

say have table high-scores:

id : primary key username : string score : int 

user names , scores can repeating, id unique each person. have index high-scores fast:

unique scores ( score, username, id ) 

how can rows below given person? 'below' mean go before given row in index.

e.g. ( 77, 'name7', 70 ) in format ( score, username, id ) want retrieve:

77, 'name7', 41 77, 'name5', 77 77, 'name5', 21 50, 'name9', 99 

but not

77, 'name8', 88 or 77, 'name7', 82 or 80, 'name2', 34 ... 

here's 1 way result:

select t.score      , t.username      , t.id   scores t  ( t.score < 77 )      or ( t.score = 77 , t.username < 'name7' )     or ( t.score = 77 , t.username = 'name7' , t.id < 70 )  order     t.score desc      , t.username desc      , t.id desc 

(note: order clause may mysql decide use index avoid "using filesort" operation. index "covering" index query, we'd expect see "using index" in explain output.)

---

i ran quick test, , in environment, perform range scan of index , avoids sort operation.

explain output

id  select_type table type  possible_keys      key        rows                      --  ----------- ----- ----- ------------------ ---------- ---- --------------------------  1  simple      t     range primary,scores_ux1 scores_ux1    3 using where; using index  

---

(you may want add limit n query, if don't need return rows satisfy criteria.)

if have unique id of row, avoid specifying values in table doing join. given data in question:

here use second reference same table, row id=70, , join rows "lower".

select t.score      , t.username      , t.id   scores k   join scores t     on ( t.score < k.score )      or ( t.score = k.score , t.username < k.username )     or ( t.score = k.score , t.username = k.username , t.id < k.id )  k.id = 70  order     t.score desc      , t.username desc      , t.id desc  limit 1000 

the explain query shows mysql using covering index , avoiding sort operation:

id select_type table type  possible_keys      key         rows -- ----------- ----- ----- ------------------ ----------  ---- ------------------------  1 simple      k     const primary,scores_ux1 primary       1  1 simple      t     range primary,scores_ux1 scores_ux1    3  using where; using index