Yes, I can think of two things you can try in order to speed up your
query. First - try the STRAIGHT JOIN clause with one small but critical
change to your statement(http://dev.mysql.com/doc/mysql/en/SELECT.html):
SELECT STRAIGHT JOIN *
FROM a
LEFT JOIN b ON a.a_id = b.a_id
AND a.timestamp BETWEEN 20040101000000 AND 20040101235959
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
GROUP BY c.d_id, c.e_id
The second thing to try would be to pre-calculate (hand optimize) the
different stages of your query (don't join all of the tables together all
at once). Although, when I started to do it myself, I was mimicking what
the STRAIGHT JOIN should do for us automatically. So I guess I really have
only one suggestion, hmmph...
It worked wonders for someone else recently, I think it's what you need
for this situation.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eamon Daly" <[EMAIL PROTECTED]> wrote on 07/30/2004 03:34:37 PM:
> 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]
>
