Re: JOIN/WHERE and index confusion

[Michael Stassen]

You've yet to show us the output of EXPLAIN.  You've summarized, but you 
haven't showed us.  It's difficult to help without all the data.  Perhaps we 
should start with the EXPLAIN for your simpler query:

  EXPLAIN SELECT * FROM a, b
  WHERE a.a_id = b.a_id
    AND a.timestamp BETWEEN 20040101000000 AND 20040101235959;
Then post the result of running EXPLAIN on the more complicated query.
In the meantime, I'll speculate.  I'd expect you need
  1) an index on a.timestamp for the range
  2) an index on b.a_id for the join to b
  3) indexes on c.c_id, d.d_id, and e.e_id for the joins to c, d, and e
  4) a multi-column index on (d_id, e_id) for table c would help with the
     GROUP BY.
You've indicated you have 1 and 3.  Do you have 2 and 4?
Michael
Eamon Daly wrote:
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]