List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 15 2005 3:27pm
Subject:Re: LEFT JOINS same data twice?
View as plain text  
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


Attachment: [text/html]
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
Thread
LEFT JOINS same data twice?Chris Knipe13 Apr
  • Re: LEFT JOINS same data twice?Peter Brawley13 Apr
  • Re: LEFT JOINS same data twice?Rhino13 Apr
  • Re: LEFT JOINS same data twice?Roger Baklund13 Apr
  • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
    • Re: LEFT JOINS same data twice?SGreen15 Apr
      • Re: LEFT JOINS same data twice?Peter Brawley15 Apr
      • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
        • Re: LEFT JOINS same data twice?SGreen15 Apr
          • Re: LEFT JOINS same data twice?SGreen15 Apr