Timestamp is the only field in the where clause, and you are selecting all fields.
The second index would require more index reads, and the third can't be used
to satisfy the where clause.
Eamon Daly wrote:
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
http://lists.mysql.com/[EMAIL PROTECTED]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:
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]