7.2.9 How MySQL Optimizes LIMIT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sodface
Forum Newbie
Posts: 4
Joined: Sat Dec 06, 2003 10:02 pm

7.2.9 How MySQL Optimizes LIMIT

Post by sodface »

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:

Code: Select all

$memscores=$DB_site->query("SELECT * FROM contest1 ORDER BY score DESC, EnteredOn ASC LIMIT ".$offset.",20");
I am using a loop counter after the query to assign the ranking numbers.

The MySql manual says:
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.
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.

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;
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

No, I believe it will not break if you are doing it the way you are doing.

I interpret the MySQL documentation as saying that it will sort until it finds the necessary rows you've requested.

If you've requested rows 900-930, it will ensure that it's found 900-930 THEN it will stop sorting. Even if it has to sort right to the end of the table to find the very last record you've requested, it will sort.
sodface
Forum Newbie
Posts: 4
Joined: Sat Dec 06, 2003 10:02 pm

Post by sodface »

Thanks for the response. So you are saying that because of the nature of the sorting algorithm, at a certain point, MySql knows that all values beyond the row limit requested are smaller but not necessarily sorted and will stop sorting? If this is the case, then I'm happy with that.

I just couldn't help wondering what would happen if my high score was at row 25,000 and I was requesting rows 1-20 sorted ascending.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

Yes, that's what I'm saying.

If MySQL did it any other way, so-o many people would have issues with that sorting method.
Post Reply