MySQL LIMIT + ORDER possible performance bug

Hi, I made a huge SELECT query, ordering by name (varchar 50). The query was very time consuming, though it only gave back about 100 or so entries (of a total of 60,000).

However, making the same SELECT with a LIMIT command was 4-plus times faster.

That is very surprising, because the number (N) I used in LIMIT was dynamical, made with a previous SELECT COUNT(*) query (without ORDER BY). So I obtained the same results using LIMIT and without LIMIT.

So we have two identical query results, but if you make an additional (previous) SELECT COUNT, you get 4 times faster speed. What is the reason for that? Theoretically, you should go faster with one query, but for some reason the LIMIT was speeding things, even if the results displayed were identical.

Thanks in advance.

I assume you made the SELECT COUNT(*) query first... is that right?

BTW, you don't have to do SELECT COUNT(*). You can speed it up a little by doing SELECT COUNT(field).

The 60,000 record select count query is fast because really, 60,000 records really isn't a lot to count straight down through. Not to mention you probably have it indexed which should be helping you out.

From the manual:

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.

As soon as MySQL has sent the first # rows to the client, it will abort the query (if you are not using SQL_CALC_FOUND_ROWS).


Also, you may want to run your query through:

EXPLAIN SELECT select_options

Thanks for your reply. Well, in fact the actual query was done in two correlated tables, that is because it was time demanding.

You are right about COUNT(field), I wrote COUNT(*) for simplicity.

About the quotes from the manual, I still don't understand it. In order to make the LIMIT with the correct entries, it MUST make the Order - BEFORE !!- applying the LIMIT N. So still the difference in times in not understandable. Why don't MySQL internal engine makes the first SELECT COUNT(field) silently ALWAYS that it encounters a SELECT...ORDER statement???

Cesar