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>) FROM sample JOIN interface ON > (sample.interface=interface.id <http://interface.id>) WHERE > 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>) FROM sample WHERE > interface=(SELECT interface.id <http://interface.id> FROM interface > WHERE 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>) FROM sample WHERE interface IN > (SELECT interface.id <http://interface.id> FROM interface WHERE > 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>) FROM sample FORCE INDEX > (sample_interface_id) WHERE interface=(SELECT interface.id > <http://interface.id> FROM interface WHERE interface.name > <http://interface.name>='accounting-total') > -- explicitly specifying the key > SELECT MAX(sample.id <http://sample.id>) FROM sample IGNORE INDEX > (sample_timestamp) WHERE interface=(SELECT interface.id > <http://interface.id> FROM interface WHERE 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 > 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 -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org