7.2.9 How MySQL Optimizes LIMIT
Posted: Wed Jan 14, 2004 10:11 pm
I have one table for a contest that stores some user info and a score.
I am displaying results 20 at a time sorted by score starting with the largest score and then descending with this select query:
I am using a loop counter after the query to assign the ranking numbers.
The MySql manual says:
Is this right? If so, when does this optimization kick in? I have 25 test entries in the table right now and my original select statement is working correctly even though some of the highest scores are at the end of the table (beyond row 20).
I wouldn't want things to work at first and then break after the table exceeded some certain size.
I thought about throwing a HAVING score>0 in there since it implies that those optimizations aren't used when using HAVING but the manual also explicitly says:
I am displaying results 20 at a time sorted by score starting with the largest score and then descending with this select query:
Code: Select all
$memscores=$DB_site->query("SELECT * FROM contest1 ORDER BY score DESC, EnteredOn ASC LIMIT ".$offset.",20");The MySql manual says:
If I read what the manual says correctly, in some cases my ranking method will break because mysql will do the sort after it's retrieved the first 20 rows instead of first sorting the whole table.In some cases MySQL will handle the query differently when you are using LIMIT row_count and not using HAVING:
If you use LIMIT row_count with ORDER BY, MySQL will end the sorting as soon as it has found the first row_count lines instead of sorting the whole table.
Is this right? If so, when does this optimization kick in? I have 25 test entries in the table right now and my original select statement is working correctly even though some of the highest scores are at the end of the table (beyond row 20).
I wouldn't want things to work at first and then break after the table exceeded some certain size.
I thought about throwing a HAVING score>0 in there since it implies that those optimizations aren't used when using HAVING but the manual also explicitly says:
The HAVING clause can refer to any column or alias named in the select_expression. It is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.) Don't use HAVING for items that should be in the WHERE clause. For example, do not write this:
mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;
Write this instead:
mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;