Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp);
EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 20040101000000 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? ____________________________________________________________ Eamon Daly ----- Original Message ----- From: "Eamon Daly" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion > 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 > > > > -- > 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]
