[debian-mysql] Bug#591271: Bug#591271: Explanation and fix

Nicholas Bamber nicholas at periapt.co.uk
Fri Mar 9 22:08:02 UTC 2012


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-maint at 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





More information about the pkg-mysql-maint mailing list