Re: Simple Query - No Join - But Temp. Table and file Sort :(

[Toa Sty]

Hi again Heiko,

Think I have a solution for you, at least if you're on MySQL 5. (not sure about support in earlier versions)

Quote

I cant remove the "Greater" operator, but test without it pointed out in no performance increase, neither of dropping temp table or filesort

Hmm that's very odd. I'm testing a very similar query here and

 select col1, col2 from mytable where col1='blah' group by col2 

works without filesort or temp table if there's an index on (col1,col2)
Have you run analyze table recently?

Quote

I want to know all locations for which a record exists in the table (actually its a event table) and where the event-date is >= Today

You could:

SELECT max(e.evdate), e.lockey, e.loc
FROM sc_events e
WHERE e.evdate >=1161428363
GROUP BY e.lockey, e.loc;

If you add an index on (lockey, loc, evdate), then this should show 'Using index for group-by' in the 'extra' explain output column and run very nicely indeed without using filesort or temp table.

Note that the value of max(e.evdate) will not necessarily correspond to the values you see for lockey or loc, it will just be the highest for that lockey/loc combination. (also note that for some reason if you don't bother selecting MAX(e.evdate) the query will optimize badly - I don't understand why MySQL misses this optimisation opportunity but there you go - BTW I'm using mysql 5.0.20a here)

For more info see: http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html



If you want, given lockey always matches loc, to keep your indexes small you might just want to:
Add an index on (lockey, evdate) instead of (lockey,loc,evdate).
then

SELECT max(e.evdate), e.lockey
FROM sc_events e
WHERE e.evdate >=1161428363
GROUP BY e.lockey;

then pull the loc values out with a separate query or via a selfjoin.

HTH,
Toasty

PS If this isn't giving you the expected results then explain plans, show create table outputs etc in [ code ] tags would be very useful.

-----------------------------------------
email: 'toasty'*3 at gmail



Edited 1 time(s). Last edit at 10/23/2006 06:43AM by Toa Sty.