MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:July 28 2004 3:58pm
Subject:JOIN/WHERE and index confusion
View as plain text  
Hi, all. I think I'm misunderstanding something basic about
indexes. I have a SELECT like so:

SELECT *
FROM a
LEFT JOIN b ON a.a_id = b.a_id
JOIN c ON a.c_id = c.c_id
JOIN d ON c.d_id = d.d_id
JOIN e ON c.e_id = e.e_id
WHERE a.timestamp BETWEEN 20040101000000 AND 20040101235959
GROUP BY c.d_id, c.e_id

All of the id fields are primary indexes. I've already
created an index on a.timestamp, and that works all right.
I tried creating an index on a for the SELECT:

KEY `reporting` (`a_id`,`c_id`,`timestamp`)

and an index on c for the GROUP BY:

KEY `reporting` (`c_id`,`d_id`,`e_id`)

But EXPLAIN shows that MySQL isn't even considering the key
on a, and chooses the primary key on c over my index.
Clearly I'm confused about how indexes are used in a
JOIN/WHERE situation: can anyone enlighten me?

____________________________________________________________
Eamon Daly


Thread
JOIN/WHERE and index confusionEamon Daly28 Jul
  • Re: JOIN/WHERE and index confusionEamon Daly28 Jul
  • Re: JOIN/WHERE and index confusiongerald_clark29 Jul
  • Re: JOIN/WHERE and index confusionEamon Daly29 Jul
  • Re: JOIN/WHERE and index confusiongerald_clark29 Jul
  • Re: JOIN/WHERE and index confusionEamon Daly30 Jul
    • Re: JOIN/WHERE and index confusionStefan Kuhn30 Jul
    • Re: JOIN/WHERE and index confusionSGreen30 Jul
      • Re: JOIN/WHERE and index confusionMichael Stassen30 Jul
        • Re: JOIN/WHERE and index confusionSGreen2 Aug
          • Re: JOIN/WHERE and index confusionMichael Stassen2 Aug
            • Re: JOIN/WHERE and index confusionSGreen2 Aug
    • Re: JOIN/WHERE and index confusionMichael Stassen30 Jul