10-10-2006, 02:12 PM
I'm using MySQL's EXPLAIN function to save some time...
MySQL says that:
SELECT DISTINCT `argle` , `bargle`
FROM `source`
...will result in 95967 rows, and uses "index for group-by". This is all splendid. Ditto if I do a GROUP BY instead of DISTINCT.
However,
SELECT `argle`,`bargle`,MAX (`wuppie`)
FROM `source`
GROUP BY `argle`,`bargle`
...will result in 3838678 rows, with the ugly ol' "Using temporary; Using filesort" message.
There are two indices:
idx1(`argle`,`bargle`)
idx2(`wuppie`)
What gives? Shouldn't each query collapse data by both argle and bargle, and do so using the existing indices?
Edit: Removal of the accidental jumping poo.
MySQL says that:
SELECT DISTINCT `argle` , `bargle`
FROM `source`
...will result in 95967 rows, and uses "index for group-by". This is all splendid. Ditto if I do a GROUP BY instead of DISTINCT.
However,
SELECT `argle`,`bargle`,MAX (`wuppie`)
FROM `source`
GROUP BY `argle`,`bargle`
...will result in 3838678 rows, with the ugly ol' "Using temporary; Using filesort" message.
There are two indices:
idx1(`argle`,`bargle`)
idx2(`wuppie`)
What gives? Shouldn't each query collapse data by both argle and bargle, and do so using the existing indices?
Edit: Removal of the accidental jumping poo.