StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You need a stored procedure to be able to call this with parameters:</p> <pre><code>CREATE TABLE rank (name VARCHAR(20) NOT NULL, points INTEGER NOT NULL); CREATE INDEX ix_rank_points ON rank(points, name); CREATE PROCEDURE prc_ranks(fromrank INT, tillrank INT) BEGIN SET @fromrank = fromrank; SET @tillrank = tillrank; PREPARE STMT FROM ' SELECT rn, rank, name, points FROM ( SELECT CASE WHEN @cp = points THEN @rank ELSE @rank := @rn + 1 END AS rank, @rn := @rn + 1 AS rn, @cp := points, r.* FROM ( SELECT @cp := -1, @rn := 0, @rank = 1 ) var, ( SELECT * FROM rank FORCE INDEX (ix_rank_points) ORDER BY points DESC, name DESC LIMIT ? ) r ) o WHERE rn >= ? '; EXECUTE STMT USING @tillrank, @fromrank; END; CALL prc_ranks (2, 5); </code></pre> <p>If you create the index and force <code>MySQL</code> to use it (as in my query), then the complexity of the query will not depend on the number of rows at all, it will depend only on <code>tillrank</code>.</p> <p>It will actually take last <code>tillrank</code> values from the index, perform some simple calculations on them and filter out first <code>fromrank</code> values.</p> <p>Time of this operation, as you can see, depends only on <code>tillrank</code>, it does not depend on how many records are there.</p> <p>I just checked in on <code>400,000</code> rows, it selects ranks from <code>5</code> to <code>100</code> in <code>0,004</code> seconds (that is, instantly)</p> <p><strong>Important:</strong> this only works if you sort on names in <code>DESCENDING</code> order. <code>MySQL</code> does not support <code>DESC</code> clause in the indices, that means that the <code>points</code> and <code>name</code> must be sorted in one order for <code>INDEX SORT</code> to be usable (either both <code>ASCENDING</code> or both <code>DESCENDING</code>). If you want fast <code>ASC</code> sorting by <code>name</code>, you will need to keep <strong>negative</strong> points in the database, and change the sign in the <code>SELECT</code> clause.</p> <p>You may also remove <code>name</code> from the index at all, and perform a final <code>ORDER</code>'ing without using an index:</p> <pre><code>CREATE INDEX ix_rank_points ON rank(points); CREATE PROCEDURE prc_ranks(fromrank INT, tillrank INT) BEGIN SET @fromrank = fromrank; SET @tillrank = tillrank; PREPARE STMT FROM ' SELECT rn, rank, name, points FROM ( SELECT CASE WHEN @cp = points THEN @rank ELSE @rank := @rn + 1 END AS rank, @rn := @rn + 1 AS rn, @cp := points, r.* FROM ( SELECT @cp := -1, @rn := 0, @rank = 1 ) var, ( SELECT * FROM rank FORCE INDEX (ix_rank_points) ORDER BY points DESC LIMIT ? ) r ) o WHERE rn >= ? ORDER BY rank, name '; EXECUTE STMT USING @tillrank, @fromrank; END; </code></pre> <p>That will impact performance on big ranges, but you will hardly notice it on small ranges.</p>

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