Anomie has uploaded a new change for review.
https://gerrit.wikimedia.org/r/179146
Change subject: API: Avoid MySQL filesort with
list=allpages&apfilterlanglinks=withlanglinks
......................................................................
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
---
M includes/api/ApiQueryAllPages.php
1 file changed, 17 insertions(+), 3 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/46/179146/1
diff --git a/includes/api/ApiQueryAllPages.php
b/includes/api/ApiQueryAllPages.php
index a85c9c9..e243593 100644
--- a/includes/api/ApiQueryAllPages.php
+++ b/includes/api/ApiQueryAllPages.php
@@ -168,9 +168,23 @@
$this->addTables( 'langlinks' );
$this->addWhere( 'page_id=ll_from' );
$this->addOption( 'STRAIGHT_JOIN' );
- // We have to GROUP BY all selected fields to stop
- // PostgreSQL from whining
- $this->addOption( 'GROUP BY', $selectFields );
+
+ // MySQL filesorts if we use a GROUP BY that works with
the rules
+ // in the 1992 SQL standard (it doesn't like having the
+ // constant-in-WHERE page_namespace column in there).
Using the
+ // 1999 rules works fine, but that breaks other DBs.
Sigh.
+ /// @todo Once we drop support for 1992-rule DBs, we
can simplify this.
+ $dbType = $db->getType();
+ if ( $dbType === 'mysql' || $dbType === 'sqlite' ||
+ $dbType === 'postgres' &&
$db->getServerVersion() >= 9.1
+ ) {
+ // 1999 rules, or screw-the-rules
+ $this->addOption( 'GROUP BY', array(
'page_title', 'page_id' ) );
+ } else {
+ // 1992 rules
+ $this->addOption( 'GROUP BY', $selectFields );
+ }
+
$forceNameTitleIndex = false;
}
--
To view, visit https://gerrit.wikimedia.org/r/179146
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I80b515bb06d194b146897155b318a3d1c908e8b6
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Anomie <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
