StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's one way to get the result:</p> <pre><code>SELECT t.score , t.username , t.id FROM scores t WHERE ( t.score < 77 ) OR ( t.score = 77 AND t.username < 'name7' ) OR ( t.score = 77 AND t.username = 'name7' AND t.id < 70 ) ORDER BY t.score DESC , t.username DESC , t.id DESC </code></pre> <p>(NOTE: the ORDER BY clause may help MySQL decide to use the index to avoid a "<code>Using filesort</code>" operation. Your index is a "covering" index for the query, so we'd expect to see "<code>Using index</code>" in the <code>EXPLAIN</code> output.)</p> <hr> <p>I ran a quick test, and in my environment, this does perform a range scan of the index and avoids a sort operation.</p> <p><strong>EXPLAIN OUTPUT</strong></p> <pre><code>id select_type table type possible_keys key rows Extra -- ----------- ----- ----- ------------------ ---------- ---- -------------------------- 1 SIMPLE t range PRIMARY,scores_UX1 scores_UX1 3 Using where; Using index </code></pre> <hr> <p>(You may want to add a <code>LIMIT n</code> to that query, if you don't need to return ALL the rows that satisfy the criteria.)</p> <p>If you have an unique id of a row, you could avoid specifying the values in the table by doing a join. Given the data in your question:</p> <p>Here we use a second reference to the same table, to get the row id=70, and then a join to get all the rows "lower".</p> <pre><code>SELECT t.score , t.username , t.id FROM scores k JOIN scores t ON ( t.score < k.score ) OR ( t.score = k.score AND t.username < k.username ) OR ( t.score = k.score AND t.username = k.username AND t.id < k.id ) WHERE k.id = 70 ORDER BY t.score DESC , t.username DESC , t.id DESC LIMIT 1000 </code></pre> <p>The EXPLAIN for this query also shows MySQL using the covering index and avoiding a sort operation:</p> <pre><code>id select_type table type possible_keys key rows Extra -- ----------- ----- ----- ------------------ ---------- ---- ------------------------ 1 SIMPLE k const PRIMARY,scores_UX1 PRIMARY 1 1 SIMPLE t range PRIMARY,scores_UX1 scores_UX1 3 Using where; Using index </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload