There is a 5.5 in experimental that might be worth a try (in a non-production environment).
On 09/03/12 22:19, Trey Raymond wrote: > This info is applicable to all 5.0.x and 5.1.x versions, I'm not sure > yet about improvements in 5.5 that might have made it irrelevant. > > On Fri, Mar 9, 2012 at 5:08 PM, Nicholas Bamber <nicho...@periapt.co.uk > <mailto:nicho...@periapt.co.uk>> wrote: > > reassign 591271 mysql-server > tag 591271 +moreinfo > thanks > > Trey, > Thanks for the new information. Could you confim what version > it was > obtained on? > > > On 09/03/12 21:43, Trey Raymond wrote: > > So this is what's happening here. I'll explain what it's doing > for each > > query, then show you the optimal way. > > > > SELECT MAX(sample.id <http://sample.id> <http://sample.id>) FROM > sample JOIN interface ON > > (sample.interface=interface.id <http://interface.id> > <http://interface.id>) WHERE > > interface.name <http://interface.name> > <http://interface.name>='accounting-total' > > -- This is just a symptom of the optimizer being stupid. It starts > > looking at the keys for the table, sees the first one, and notices > that > > it is a covering index - and therefore it choses to use it without > > considering that there might be a better one. It is a covering index > > because in innodb, the primary key is appended to every secondary key, > > so KEY (`interface`,`timestamp`) actually stores > > (`interface`,`timestamp`,`id`)...also note that where you have KEY > > (`interface`,`id`), you really only need to say KEY (`interface`), for > > this same reason. The problem is that it's caring so much about > using a > > key to filter that it doesn't notice there's one that could be used to > > filter AND get the max(id) easily. That's just the mysql > optimizer for > > you. Newer versions tend to be a bit smarter, but you'll always see > > this kind of thing. I bet if you switched what order those keys > were in > > in the file, it'd find the correct one first and use it. > > > > SELECT MAX(sample.id <http://sample.id> <http://sample.id>) FROM > sample WHERE > > interface=(SELECT interface.id <http://interface.id> > <http://interface.id> FROM interface > > WHERE interface.name <http://interface.name> > <http://interface.name>='accounting-total') > > -- This one is using a one-time (independent) subquery, or "derived > > table." As it only has to execute that statement once, it's very > fast. > > This is basically what mysql would do if the optimizer handled it > > right, but without join syntax optimization, so it's still not > perfect. > > It's the closest you can get without index hints or changing the > indexing. > > > > SELECT MAX(sample.id <http://sample.id> <http://sample.id>) FROM > sample WHERE interface IN > > (SELECT interface.id <http://interface.id> <http://interface.id> > FROM interface WHERE > > interface.name <http://interface.name> > <http://interface.name>='accounting-total') > > -- This is a BIG no-no. It's using a dependent subquery - note how > > saying IN implies multiple values could be returned and a range > check is > > needed. It also implies that the subquery cannot be solved before the > > main table is accessed, so no "derived table" use. In this case, > it has > > to scan the ENTIRE key on the sample table, and for EVERY record > it must > > run that subquery - you're basically executing over 6 million separate > > queries in this one statement. This is the slowest method. > Beware when > > you see "Dependent subquery" in an explain plan - there is usually a > > better way to go about it. > > > > So in conclusion, the best way to fix it is to just tell the optimizer > > to use the correct index. Either of the following two should work: > > SELECT MAX(sample.id <http://sample.id> <http://sample.id>) FROM > sample FORCE INDEX > > (sample_interface_id) WHERE interface=(SELECT interface.id > <http://interface.id> > > <http://interface.id> FROM interface WHERE interface.name > <http://interface.name> > > <http://interface.name>='accounting-total') > > -- explicitly specifying the key > > SELECT MAX(sample.id <http://sample.id> <http://sample.id>) FROM > sample IGNORE INDEX > > (sample_timestamp) WHERE interface=(SELECT interface.id > <http://interface.id> > > <http://interface.id> FROM interface WHERE interface.name > <http://interface.name> > > <http://interface.name>='accounting-total') > > -- telling it to skip that first key and find the next usable one > > > > > > _______________________________________________ > > pkg-mysql-maint mailing list > > pkg-mysql-ma...@lists.alioth.debian.org > <mailto:pkg-mysql-ma...@lists.alioth.debian.org> > > > http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-mysql-maint > > > -- > Nicholas Bamber | http://www.periapt.co.uk/ > PGP key 3BFFE73C from pgp.mit.edu <http://pgp.mit.edu> > > -- Nicholas Bamber | http://www.periapt.co.uk/ PGP key 3BFFE73C from pgp.mit.edu -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org