Using LIMIT in a SELECT statement which does a JOIN

[Martin Wright]

Hello,

Hopefully someone has the answer to this ...

If I have a SELECT statement which does a JOIN and also uses LIMIT to limit the number of records returned, does MySQL know to only do the JOIN on records that will actually be returned or does it do the JOIN for all records even if they not in the records identified by the LIMIT statement? And if it optimises properly for a normal querywhat about if I specify SQL_CALC_FOUND_ROWS?

Obviously if the WHERE clause uses fields in the JOIN target then it has to do the join on all records, but otherwise it ought to be smart enough to only do the JOIN on records it is actually returning?

By way of illustration, suppose the following query returns 200 rows:

Select A FROM Table_1 WHERE B = 1

My question is, how many fetches from Table_2 will the following query do:

Select Table_1.A,Table_2.D FROM Table_1 INNER JOIN Table_2 ON Table_1.C = Table_2.C WHERE Table_1.B = 1 LIMIT 1,30

Will it do 30 or 200 fetches from Table_2?

What about this one where I set a starting row:

Select Table_1.A,Table_2.D FROM Table_1 INNER JOIN Table_2 ON Table_1.C = Table_2.C WHERE Table_1.B = 1 LIMIT 30,60

Will it do 30, 60 or 200 fetches from Table_2?

And finally, how many fetches from Table_2 will this one do:

Select SQL_CALC_FOUND_ROWS Table_1.A,Table_2.D FROM Table_1 INNER JOIN Table_2 ON Table_1.C = Table_2.C WHERE Table_1.B = 1 LIMIT 30,60

Thanks!

Martin.