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 |
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]