MySQL Forums :: General :: Using LIMIT in a SELECT statement which does a JOIN


Advanced Search

Using LIMIT in a SELECT statement which does a JOIN
Posted by: Martin Wright ()
Date: July 02, 2009 10:01AM

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.

Options: ReplyQuote


Subject Written By Posted
Using LIMIT in a SELECT statement which does a JOIN Martin Wright 07/02/2009 10:01AM
Re: Using LIMIT in a SELECT statement which does a JOIN Rick James 07/03/2009 02:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.