HomePhabricator

API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinks

Description

API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinks

I'm not sure whether r44584 didn't go far enough or if MySQL's behavior
has changed since 2008, but MySQL is now filesorting when a
constant-in-WHERE field is included in GROUP BY.

If all our supported databases used the 1999 SQL standard rules for
GROUP BY[1] this would be an easy fix. But PostgreSQL before 9.1 uses
the older 1992 rules.[2] And then there's Oracle and MSSQL, which aren't
listed as supported[1] but are still in the code. Simplest thing to do
is probably to check if we're on MySQL, Sqlite, or Postgres >= 9.1 and
use the 1999 rules, and otherwise use the older rules.

[1]: Basically "any non-aggregate field in the SELECT must be

functionally dependent on the grouped-by fields", meaning if you
include the primary key you're good.

[2]: Basically "any non-aggregate field in the SELECT must be in the

GROUP BY".

[3]: https://www.mediawiki.org/wiki/Manual:Installation_requirements#Database_server

Bug: T78276
Change-Id: I80b515bb06d194b146897155b318a3d1c908e8b6

Details

Provenance
AnomieAuthored on
tstarlingCommitted on Dec 19 2014, 12:08 AM
Parents
rMW1961ac1cbfdf: Merge "Use generalizeSQL for DBPerformance log entries"
Branches
Unknown
Tags
Unknown
ChangeId
I80b515bb06d194b146897155b318a3d1c908e8b6