MySQL Forums
Forum List  »  Newbie

Re: Optimizing this join query
Posted by: Rick James
Date: April 10, 2014 02:48PM

> I added this forced index: FORCE INDEX (index_products_on_sku_canonical)

Usually using USE/FORCE/IGNORE INDEX is a mistake. It may help today's query, but hurt tomorrow's query -- with a different value, or a bigger table, or a different phase of the moon.

> and it's showing significant performance increases (over 50%!!!!).

Did you run the query twice -- so as to mask the effect of I/O caching?

> It's weird - when I do an explain, the old query scans 5 times less rows but I guess it has something to do with

EXPLAIN's values are rather fuzzy. A 5x change _may_ be due to such. Or you may have a useful clue. Show us the SHOW CREATE TABLE and EXPLAINs; we may be able to elaborate further.

> Using index condition; Using where; Using temporary; Using filesort

"index condition" probably refers to the new optimization feature:
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
What version are you running? Your test case illustrates a "regression". Consider posting on bugs.mysql.com. (It will need schema, sample data, test case, etc.)

(In reading that link, note that MySQL is implemented in two layers: the "MySQL server" and the "storage engine" (eg, InnoDB).) ICP usually saves CPU time, and may save I/O.

Another thing that might be handy (for slow and fast versions)...
FLUSH SESSION STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';
That should give some clues into how the query is being executed.

Options: ReplyQuote


Subject
Written By
Posted
April 07, 2014 12:50PM
Re: Optimizing this join query
April 10, 2014 02:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.