![]() |
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... ![]() 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 |