MacResource
MySQL Query confusion - Printable Version

+- MacResource (https://forums.macresource.com)
+-- Forum: My Category (https://forums.macresource.com/forumdisplay.php?fid=1)
+--- Forum: Tips and Deals (https://forums.macresource.com/forumdisplay.php?fid=3)
+--- Thread: MySQL Query confusion (/showthread.php?tid=20314)



MySQL Query confusion - Greg the dogsitter - 10-10-2006

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.


Re: MySQL Query confusion - Greg the dogsitter - 10-10-2006

Okay; it looks like I need to create a single index on argle, bargle, and wuppie, in order for this to work quickly. At least, that's what my test on a smaller, temporary table showed.


Re: MySQL Query confusion - mattkime - 10-10-2006

that definitely makes sense although you had me really confused.


Re: MySQL Query confusion - Greg the dogsitter - 10-10-2006

[quote mattkime]that definitely makes sense although you had me really confused.
I get that a lot.

Well, more the second part than the first, usually.


Re: MySQL Query confusion - Paul F. - 10-10-2006

Greg.... sometimes, like this, I think you need to turn off the computer for a while and go outside and play with the wuppies...

Your post is deeply rooted in geekdom... Big Grin


Re: MySQL Query confusion - Greg the dogsitter - 10-10-2006

If only we had wuppies here...


Re: MySQL Query confusion - TheTominator - 10-10-2006

[quote Greg the dogsitter]If only we had wuppies here...
(GtDS)
I could wile away the hours
Out playing 'mungst the flowers
Cavortin' in the rain
And his belly I'd be scratchin'
While his leg was busy slappin'
If I only had a Dane

I would wipe up any piddle
For any individ'le
Before it makes a stain

(TheTominator)
With the pupppies you'd be stinkin'
(A Shar-Pei would be all wrinklin')
If you only had a Dane

(GtDS)
Oh, I would tell you why
The drool is on the floor
I could pick up doggie toys I never saw before
And then I'd sit and find some more

I would not be just a nuffin'
Plush toys all full of stuffin'
The carpet full of stain
I would dance and be merry
Life would be a ding-a-derry
If I only had a Dane