Re: JOIN/WHERE and index confusion

[gerald_clark]

Why should it use any other?
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

 

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]