I would assume it would use reporting_id_t, since the WHERE clause has both a.a_id and a.timestamp in it.
____________________________________________________________ Eamon Daly ----- Original Message ----- From: "gerald_clark" <[EMAIL PROTECTED]> To: "Eamon Daly" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 29, 2004 8:04 AM Subject: Re: JOIN/WHERE and index confusion > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
