So, to confirm, short of indexing a.timestamp (which I've done) there's no way to speed up the original query?
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 This report takes over an hour when looking at just one month's worth of data. It's brutal. Any and all suggestions would be appreciated. ____________________________________________________________ Eamon Daly ----- Original Message ----- From: "gerald_clark" <[EMAIL PROTECTED]> To: "Eamon Daly" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 29, 2004 10:54 AM Subject: Re: JOIN/WHERE and index confusion > No. a.a_id is used in the join to b, not for selecting records in a. > > Eamon Daly wrote: > > >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]
