Two general MySQL questions..

MySQL & MariaDB Other MySQL® Questions
1

Hi,

  1. When using SQL_CALC_FOUND_ROWS combined with a LIMIT and an ORDER BY. Does MySQL stop ordering after the LIMIT is reached? Or are all results ordered by MySQL while getting the SQL_CALC_FOUND_ROWS?
    If this is the case it probably is faster to do a SELECT COUNT(*) instead…without an ORDER BY.

  2. When MySQL (regular INNER??) JOINs two tables… Does it first do a SELECT on table1 and a SELECT on table2 and then combine the results? Or does it do a SELECT on table1, join it… and do another select?

Thanks!

2

  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.

3

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!

4

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