At http://dev.mysql.com/doc/mysql/en/explain.html the paragraph starting with "EXPLAIN returns a row of information for each table used in the SELECT statement" explains the process briefly. In general MySQL reads/finds rows while also satisfying other conditions. In your example below, if there's an index on A.lastname, MySQL may use it to find all rows WHERE A.lastname = 'doe'. For each A.id of these rows, the corresponding B row is read, probably by using an index on B.id. So if there were 3 'doe's, MySQL would read A, B, A, B, A, B (presuming a 1-to-1 relationship between A.id and B.id).
If there were no index on A.lastname, MySQL would probably table scan A. When it hit a row WHERE lastname = 'doe', it would use that row's id to read the corresponding B row. Therefore MySQL has to read 1M A rows because a 'doe' row could be at the very end, verses reading 3 A rows with an index on A.lastname. (Alternatively, MySQL might table scan B and for every B.id read the corresponding A.id row keeping only those where A.lastname = 'doe' also.) If A.id and B.id were a 1-to-N relationship, it changes things again since for every 'doe' A.id row, there could be N many B rows. The read sequence could then be like A, B, B, B, A, B, B, etc. In either case MySQL does not create a cross-product of the tables. It may read a cross-product's worth of rows if there are no indexes anywhere so it has to scan all tables. But in general MySQL reads only what it has to, filters by the given conditions, and builds the final result set as it goes. -Daniel On Wed, 2005-05-25 at 21:01 -0700, James Tu wrote: > What does MySQL do internally when you perform a LEFT JOIN? > > Let's say you have two tables: > Table A has 1,000,000 rows > Table B has 5,000 rows > > When you perform the following LEFT JOIN: > Select A.*, B.* > FROM A, B > WHERE > A.lastname = 'doe' AND A.id <http://A.id> = B.id <http://B.id> > > What does MySQL do internally? > Does it first create some sort of CROSS JOIN with the two tables (resulting > in a 5,000,000,000 row table) > and then finding the matching rows based on the WHERE clause? > > -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
