I did not follow the discussion, but I would say you need to have a combined index on a.timestamp, a.a_id and a.c_id. Sorry if you already tried this. Stefan
Am Friday 30 July 2004 21:34 schrieb Eamon Daly: > 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 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
