> 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.