From: Peter Brawley
Date: April 15 2005 3:27pm
Subject: Re: LEFT JOINS same data twice?
List-Archive: http://lists.mysql.com/mysql/182679
Message-Id: <425FDD5F.4000606@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-425FDD606B14======="
--=======AVGMAIL-425FDD606B14=======
Content-Type: multipart/alternative; boundary=------------090203060606090707080704
--------------090203060606090707080704
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Shawn,
>Even though the docs say that only 1 index is ever used per
>query, I believe that each JOIN can also make use of an index if it
exists
>(this belief is based on the results of the EXPLAIN(s) of queries
>containing JOINed tables. However, I could be completely wrong in this
>belief.).
You appear to be right. A query with 2 self-joins on one table uses both
indexes on the table:
EXPLAIN SELECT c.customerID, c1.customerID, c2.customerID
FROM customers c
INNER JOIN customers c1 ON c.customerID=c1.customerID
INNER JOIN customers c2 ON C.companyName=c2.companyName\G
id: 1
select_type: SIMPLE
table: C2
type: index
possible_keys: CompanyName
key: CompanyName
.....
id: 1
select_type: SIMPLE
table: C
type: ref
possible_keys: PRIMARY,CompanyName
key: CompanyName
.....
id: 1
select_type: SIMPLE
table: C1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
Perhaps the docs should say "one index per table /or alias/."
PB
--------------090203060606090707080704
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Shawn,
>Even though the docs say that only 1 index is ever used per
>query, I believe that each JOIN can also make use of an index if it
exists
>(this belief is based on the results of the EXPLAIN(s) of queries
>containing JOINed tables. However, I could be completely wrong in
this
>belief.).
You appear to be right. A query with 2 self-joins on one table uses
both indexes on the table:
EXPLAIN SELECT c.customerID, c1.customerID, c2.customerID
FROM customers c
INNER JOIN customers c1 ON c.customerID=c1.customerID
INNER JOIN customers c2 ON C.companyName=c2.companyName\G
id: 1
select_type: SIMPLE
table: C2
type: index
possible_keys: CompanyName
key: CompanyName
.....
id: 1
select_type: SIMPLE
table: C
type: ref
possible_keys: PRIMARY,CompanyName
key: CompanyName
.....
id: 1
select_type: SIMPLE
table: C1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
Perhaps the docs should say "one index per table or alias."
PB
--------------090203060606090707080704--
--=======AVGMAIL-425FDD606B14=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.11 - Release Date: 4/14/2005
--=======AVGMAIL-425FDD606B14=======--