1) Good question. I'm not 100% sure but I would assume it does not, note even if you use LIMIT without SQL_CALC_FOUND_ROWS MySQL can only stop sorting on very last pass, so most work is performed anyway.

2) SELECT is SQL lever operation so it is neither, what happens it performs scan/lookup in the first table and for each row it performs lookup in the second table. EXPLAIN can be used to see what kind of lookup is used in both cases.

Hi (again ) (as I have two treads with your answers)) Peter,

I really appreciate your help on this forum! Thanks for the answer and thanks for the forum itself!

> note even if you use LIMIT without SQL_CALC_FOUND_ROWS MySQL can only stop
> sorting on very last pass, so most work is performed anyway.

This brings up something I've been asking mysql to add for a while. If you have a query that has 5 sorts then a limit:

order by 1,2,3,4,5 limit 10;

MySQL should order column 1 then take the top 10 entries plus any additional entries that are the same as the 10th. Then the additional sorts could just sort on this smaller set. For the majority of cases this would hugely cut down on the speed of sorts.

I also noticed that having two sorts on a table can be over twice as slow as the single sort which is another reason to consider the limit when optimizing these.

-Ryan

Ryan,

You're right Sort with Limit can be done way better

There are also numerous other sort optimizations which can be implemented, for example

KEY(A)

ORDER BY A,B

One can still use index based sort and only reorder values for constant A

Order with Join also can be improved to use the index or only partial sort rather than filesort in many cases.