https://bugzilla.wikimedia.org/show_bug.cgi?id=24782
Brad Jorsch <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected] --- Comment #16 from Brad Jorsch <[email protected]> --- Regarding the indexes, it looks like it's sort of an issue. Normally, MySQL will insist on filesorting due to the extra field in the ORDER BY, which is certainly an issue for recentchanges because it doesn't seem like it's smart enough to fetch just the first 50 by the index plus only those extra are "tied" for 50th place in the non-unique index.[1] But with the way InnoDB indexes work, one index (usually the primary key) is effectively appended to every other index[2] and MySQL will take advantage of this when performing queries. So for the recentchanges changes table, since we use InnoDB and the primary key is (rc_id), the rc_timestamp index is secretly (rc_timestamp,rc_id) even though it is defined as just (rc_timestamp). So a query SELECT ... FROM recentchanges WHERE rc_timestamp > '...' ORDER BY rc_timestamp, rc_id LIMIT 50 will not have to filesort under these conditions, and the EXPLAIN output reflects this. Whether we want to *rely* on this behavior, I don't know. [1]: https://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html [2]: https://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
