Re: indices not used

[Rick James]

I guess this is MyISAM, not InnoDB?

It is more efficient to do a table scan (read all the MYD file) and sort the result, than to bounce between the MYI (with the index) and the MYD. Hence the preference for ignoring the index on
SELECT * FROM tbl ORDER BY some_key_field;

You products query would have done the same if it saw (by probing the index) that you were probably going to get more than xx% of the table. (xx is somewhere between 10 and 30, depending on the phase of the moon.)

ALTER TABLE product ORDER BY factor;
will "cluster" rows so that nearby values of y are probably in the same disk block. This can be a big benefit if the table is huge and you fetch values near y. It is a benefit because of decreasing disk I/O; the explain plan won't change.

You must always use ORDER BY if you want to be sure that the data is returned in that order. Granted, there are cases where the data seems to always be correctly ordered, but that is today; it could change tomorrow.

ORDER BY is optimized out if the optimizer can guarantee the order without actually doing a sort. ALTER will not provide that guarantee.