Yep, weird with a capital W.
Take two tables. Un-optimized indexes are present on both tables. EXPLAIN says 45 rows will be retrieved from one table, 5 from the other. The 45 will invoke where, the 5 invokes temporary and filesort.
Alter the index on the table returning 5 rows (it’s a pivot table).
Now explain says 75, 4, with 75 invoking where, temporary and filesort and 4 invoking where.
Alter the index on the table returning 75 rows so that the index hit picks up the data that’s getting pivoted.
Now explain says 158, 4 with 158 invoking where, index, temporary, and filesort and 4 invoking where.
The weird part? The last modification is the fastest, even with no_sql_cache invoked. More rows to be worked with (arguably it’s still a small number), but it’s faster.
