Meant to send this to the list.
Christopher J. Noyes
- Original Message -
From: [EMAIL PROTECTED]
To: Roger Baklund [EMAIL PROTECTED]
Sent: Friday, May 27, 2005 9:55 PM
Subject: Re: Inner workings of a JOIN
Does it first create some sort of CROSS JOIN with the two tables
(resulting
in a 5,000,000,000 row table)
this is what is called a cartesian join, which only results when the two
tables are not correctly linked.
First you need to understand that most databases frequently use a btree
type index which will allow any piece of data to be found with four or
less seeks.
What happens is parts of your query that the optimizer thinks can narrow
the query down, get applied to the index which identifies the rows that
match, and the next terms get applied to those rows, when it comes to the
join, it does a lookup on the index of the second table based on the
columns in the first table that were found and brings back the rows that
match, it it winds up being a one to many, the number of rows grows. That
is a reason why you need well designed indexes on your tables for the
queries that you commonly run. If there are no indexes on important
columns, particularly where there are joins, it can cause a full table
scan which is very slow, rather than an index range scan, which is fast.
Christopher J. Noyes
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Cc: James Tu [EMAIL PROTECTED]
Sent: Thursday, May 26, 2005 8:32 AM
Subject: Re: Inner workings of a JOIN
James Tu wrote:
What does MySQL do internally when you perform a LEFT JOIN?
You can read about it here:
URL: http://dev.mysql.com/doc/mysql/en/left-join-optimization.html
If you need more details, you could read the source...
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
eh... that's not a LEFT JOIN...?
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?
If there is no index on A.lastname and B.id, probably yes...
Use the EXPLAIN SELECT command to see how MySQL plan to solve the query.
URL: http://dev.mysql.com/doc/mysql/en/explain.html
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
