Aaron Schulz has uploaded a new change for review.
https://gerrit.wikimedia.org/r/117373
Change subject: Redid WhatLinksHere query and added a _from_namespace field
......................................................................
Redid WhatLinksHere query and added a _from_namespace field
* Also tweaked the query so MySQL avoids doing a page_name
index scan when it should start with the link table index
* Added population script (triggered by update.php)
* Also removed uniqueness from some indexes where it is redundant
bug: 60618
Change-Id: Icca99b6ae0ef76cb77695faf82c615516191da36
---
M includes/deferred/LinksUpdate.php
M includes/installer/DatabaseUpdater.php
M includes/installer/MysqlUpdater.php
M includes/installer/PostgresUpdater.php
M includes/installer/SqliteUpdater.php
M includes/specials/SpecialMergeHistory.php
M includes/specials/SpecialWhatlinkshere.php
A maintenance/archives/patch-il_from_namespace.sql
A maintenance/archives/patch-pl_from_namespace.sql
A maintenance/archives/patch-tl_from_namespace.sql
M maintenance/tables.sql
11 files changed, 100 insertions(+), 51 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/73/117373/1
diff --git a/includes/deferred/LinksUpdate.php
b/includes/deferred/LinksUpdate.php
index 79232e5..6082542 100644
--- a/includes/deferred/LinksUpdate.php
+++ b/includes/deferred/LinksUpdate.php
@@ -358,6 +358,7 @@
foreach ( $diffs as $dbk => $id ) {
$arr[] = array(
'pl_from' => $this->mId,
+ 'pl_from_namespace' =>
$this->mTitle->getNamespace(),
'pl_namespace' => $ns,
'pl_title' => $dbk
);
@@ -379,6 +380,7 @@
foreach ( $diffs as $dbk => $id ) {
$arr[] = array(
'tl_from' => $this->mId,
+ 'tl_from_namespace' =>
$this->mTitle->getNamespace(),
'tl_namespace' => $ns,
'tl_title' => $dbk
);
@@ -400,6 +402,7 @@
foreach ( $diffs as $iname => $dummy ) {
$arr[] = array(
'il_from' => $this->mId,
+ 'il_from_namespace' =>
$this->mTitle->getNamespace(),
'il_to' => $iname
);
}
diff --git a/includes/installer/DatabaseUpdater.php
b/includes/installer/DatabaseUpdater.php
index 3f2e2cb..7078c02 100644
--- a/includes/installer/DatabaseUpdater.php
+++ b/includes/installer/DatabaseUpdater.php
@@ -1063,4 +1063,12 @@
$wgContentHandlerUseDB = $this->holdContentHandlerUseDB;
}
}
+
+ /**
+ * Fill *_from_namespace fields in links tables
+ */
+ protected function populateBacklinkNamespaces() {
+ $task = $this->maintenance->runChild(
'PopulateBacklinkNamespaces', 'populateBacklinkNamespaces.php' );
+ $task->execute();
+ }
}
diff --git a/includes/installer/MysqlUpdater.php
b/includes/installer/MysqlUpdater.php
index bcd50b3..197d487 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -250,6 +250,10 @@
array( 'addIndex', 'logging', 'log_user_text_time',
'patch-logging_user_text_time_index.sql' ),
array( 'addField', 'page', 'page_links_updated',
'patch-page_links_updated.sql' ),
array( 'addField', 'user', 'user_password_expires',
'patch-user_password_expire.sql' ),
+ array( 'addField', 'pagelinks', 'pl_from_namespace',
'patch-pl_from_namespace.sql' ),
+ array( 'addField', 'templatelinks',
'tl_from_namespace', 'patch-tl_from_namespace.sql' ),
+ array( 'addField', 'imagelinks', 'il_from_namespace',
'patch-il_from_namespace.sql' ),
+ array( 'populateBacklinkNamespaces' )
);
}
diff --git a/includes/installer/PostgresUpdater.php
b/includes/installer/PostgresUpdater.php
index c5c10b3..e906372 100644
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -405,6 +405,10 @@
array( 'addPgField', 'recentchanges', 'rc_source',
"TEXT NOT NULL DEFAULT ''" ),
array( 'addPgField', 'page', 'page_links_updated',
"TIMESTAMPTZ NULL" ),
array( 'addPgField', 'mwuser', 'user_password_expires',
'TIMESTAMPTZ NULL' ),
+ array( 'addPgField', 'pagelinks', 'pl_from_namespace',
'INTEGER NOT NULL DEFAULT 0' ),
+ array( 'addPgField', 'templatelinks',
'tl_from_namespace', 'INTEGER NOT NULL DEFAULT 0' ),
+ array( 'addPgField', 'imagelinks', 'il_from_namespace',
'INTEGER NOT NULL DEFAULT 0' ),
+ array( 'populateBacklinkNamespaces' )
);
}
diff --git a/includes/installer/SqliteUpdater.php
b/includes/installer/SqliteUpdater.php
index ea19efa..8a19de0 100644
--- a/includes/installer/SqliteUpdater.php
+++ b/includes/installer/SqliteUpdater.php
@@ -128,6 +128,10 @@
array( 'addIndex', 'logging', 'log_user_text_time',
'patch-logging_user_text_time_index.sql' ),
array( 'addField', 'page', 'page_links_updated',
'patch-page_links_updated.sql' ),
array( 'addField', 'user', 'user_password_expires',
'patch-user_password_expire.sql' ),
+ array( 'addField', 'pagelinks', 'pl_from_namespace',
'patch-pl_from_namespace.sql' ),
+ array( 'addField', 'templatelinks',
'tl_from_namespace', 'patch-tl_from_namespace.sql' ),
+ array( 'addField', 'imagelinks', 'il_from_namespace',
'patch-il_from_namespace.sql' ),
+ array( 'populateBacklinkNamespaces' )
);
}
diff --git a/includes/specials/SpecialMergeHistory.php
b/includes/specials/SpecialMergeHistory.php
index f799fc5..f9fe69b 100644
--- a/includes/specials/SpecialMergeHistory.php
+++ b/includes/specials/SpecialMergeHistory.php
@@ -396,6 +396,7 @@
$dbw->insert( 'pagelinks',
array(
'pl_from' => $this->mDestID,
+ 'pf_from_namespace' =>
$destTitle->getNamespace(),
'pl_namespace' =>
$destTitle->getNamespace(),
'pl_title' =>
$destTitle->getDBkey() ),
__METHOD__
diff --git a/includes/specials/SpecialWhatlinkshere.php
b/includes/specials/SpecialWhatlinkshere.php
index 789ea76..baa0c57 100644
--- a/includes/specials/SpecialWhatlinkshere.php
+++ b/includes/specials/SpecialWhatlinkshere.php
@@ -102,9 +102,9 @@
*/
function showIndirectLinks( $level, $target, $limit, $from = 0, $back =
0 ) {
global $wgMaxRedirectLinksRetrieved;
+
$out = $this->getOutput();
$dbr = wfGetDB( DB_SLAVE );
- $options = array();
$hidelinks = $this->opts->getValue( 'hidelinks' );
$hideredirs = $this->opts->getValue( 'hideredirs' );
@@ -113,80 +113,84 @@
$fetchlinks = ( !$hidelinks || !$hideredirs );
- // Make the query
- $plConds = array(
- 'page_id=pl_from',
+ // Build query conds in concert for all three tables...
+ $conds['pagelinks'] = array(
'pl_namespace' => $target->getNamespace(),
'pl_title' => $target->getDBkey(),
);
- if ( $hideredirs ) {
- $plConds['rd_from'] = null;
- } elseif ( $hidelinks ) {
- $plConds[] = 'rd_from is NOT NULL';
- }
-
- $tlConds = array(
- 'page_id=tl_from',
+ $conds['templatelinks'] = array(
'tl_namespace' => $target->getNamespace(),
'tl_title' => $target->getDBkey(),
);
-
- $ilConds = array(
- 'page_id=il_from',
+ $conds['imagelinks'] = array(
'il_to' => $target->getDBkey(),
);
$namespace = $this->opts->getValue( 'namespace' );
if ( is_int( $namespace ) ) {
- $plConds['page_namespace'] = $namespace;
- $tlConds['page_namespace'] = $namespace;
- $ilConds['page_namespace'] = $namespace;
+ $conds['pagelinks']['pl_from_namespace'] = $namespace;
+ $conds['templatelinks']['tl_from_namespace'] =
$namespace;
+ $conds['imagelinks']['il_from_namespace'] = $namespace;
}
if ( $from ) {
- $tlConds[] = "tl_from >= $from";
- $plConds[] = "pl_from >= $from";
- $ilConds[] = "il_from >= $from";
+ $conds['templatelinks'][] = "tl_from >= $from";
+ $conds['pagelinks'][] = "pl_from >= $from";
+ $conds['imagelinks'][] = "il_from >= $from";
}
- // Read an extra row as an at-end check
- $queryLimit = $limit + 1;
+ if ( $hideredirs ) {
+ $conds['pagelinks']['rd_from'] = null;
+ $conds['templatelinks']['rd_from'] = null;
+ $conds['imagelinks']['rd_from'] = null;
+ } elseif ( $hidelinks ) {
+ $conds['pagelinks'][] = 'rd_from is NOT NULL';
+ $conds['templatelinks'][] = 'rd_from is NOT NULL';
+ $conds['imagelinks'][] = 'rd_from is NOT NULL';
+ }
- $options['LIMIT'] = $queryLimit;
- $fields = array( 'page_id', 'page_namespace', 'page_title',
'rd_from' );
-
- $joinConds = array( 'redirect' => array( 'LEFT JOIN', array(
- 'rd_from = page_id',
- 'rd_namespace' => $target->getNamespace(),
- 'rd_title' => $target->getDBkey(),
- 'rd_interwiki = ' . $dbr->addQuotes( '' ) . ' OR
rd_interwiki IS NULL'
- )));
+ $queryFunc = function( $dbr, $table, $fromCol ) use ( $conds,
$target, $limit ) {
+ // Read an extra row as an at-end check
+ $queryLimit = $limit + 1;
+ // Inner LIMIT is 2X in case of stale backlinks with no
page
+ $subQuery = $dbr->selectSqlText(
+ array( $table, 'redirect' ),
+ array( $fromCol, 'rd_from' ),
+ $conds[$table],
+ __CLASS__ . '::showIndirectLinks',
+ array( 'ORDER BY' => $fromCol, 'LIMIT' => 2 *
$queryLimit ),
+ array( 'redirect' => array( 'LEFT JOIN', array(
+ "rd_from = $fromCol",
+ 'rd_namespace' =>
$target->getNamespace(),
+ 'rd_title' => $target->getDBkey(),
+ 'rd_interwiki = ' . $dbr->addQuotes( ''
) . ' OR rd_interwiki IS NULL'
+ ) ) )
+ );
+ return $dbr->select(
+ array( 'page', 'temp_backlink_range' =>
"($subQuery)" ),
+ array( 'page_id', 'page_namespace',
'page_title', 'rd_from' ),
+ array(),
+ __CLASS__ . '::showIndirectLinks',
+ array( 'ORDER BY' => 'page_id', 'LIMIT' =>
$queryLimit ),
+ array( 'page' => array( 'INNER JOIN', "$fromCol
= page_id" ) )
+ );
+ };
if ( $fetchlinks ) {
- $options['ORDER BY'] = 'pl_from';
- $plRes = $dbr->select( array( 'pagelinks', 'page',
'redirect' ), $fields,
- $plConds, __METHOD__, $options,
- $joinConds
- );
+ $plRes = $queryFunc( $dbr, 'pagelinks', 'pl_from' );
}
if ( !$hidetrans ) {
- $options['ORDER BY'] = 'tl_from';
- $tlRes = $dbr->select( array( 'templatelinks', 'page',
'redirect' ), $fields,
- $tlConds, __METHOD__, $options,
- $joinConds
- );
+ $tlRes = $queryFunc( $dbr, 'templatelinks', 'tl_from' );
}
if ( !$hideimages ) {
- $options['ORDER BY'] = 'il_from';
- $ilRes = $dbr->select( array( 'imagelinks', 'page',
'redirect' ), $fields,
- $ilConds, __METHOD__, $options,
- $joinConds
- );
+ $ilRes = $queryFunc( $dbr, 'imagelinks', 'il_from' );
}
- if ( ( !$fetchlinks || !$plRes->numRows() ) && ( $hidetrans ||
!$tlRes->numRows() ) && ( $hideimages || !$ilRes->numRows() ) ) {
+ if ( ( !$fetchlinks || !$plRes->numRows() )
+ && ( $hidetrans || !$tlRes->numRows() ) && (
$hideimages || !$ilRes->numRows() )
+ ) {
if ( 0 == $level ) {
$out->addHTML( $this->whatlinkshereForm() );
diff --git a/maintenance/archives/patch-il_from_namespace.sql
b/maintenance/archives/patch-il_from_namespace.sql
new file mode 100644
index 0000000..6b67032
--- /dev/null
+++ b/maintenance/archives/patch-il_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/imagelinks
+ ADD COLUMN il_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks
(il_to,il_from_namespace,il_from);
\ No newline at end of file
diff --git a/maintenance/archives/patch-pl_from_namespace.sql
b/maintenance/archives/patch-pl_from_namespace.sql
new file mode 100644
index 0000000..2f7ff04
--- /dev/null
+++ b/maintenance/archives/patch-pl_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/pagelinks
+ ADD COLUMN pl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks
(pl_namespace,pl_title,pl_from_namespace,pl_from);
diff --git a/maintenance/archives/patch-tl_from_namespace.sql
b/maintenance/archives/patch-tl_from_namespace.sql
new file mode 100644
index 0000000..8d6c76b
--- /dev/null
+++ b/maintenance/archives/patch-tl_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/templatelinks
+ ADD COLUMN tl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks
(tl_namespace,tl_title,tl_from_namespace,tl_from);
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index ca610fc..c889966 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -470,6 +470,8 @@
CREATE TABLE /*_*/pagelinks (
-- Key to the page_id of the page containing the link.
pl_from int unsigned NOT NULL default 0,
+ -- Namespace for this page
+ pl_from_namespace int NOT NULL default 0,
-- Key to page_namespace/page_title of the target page.
-- The target page may or may not exist, and due to renames
@@ -480,7 +482,8 @@
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks
(pl_from,pl_namespace,pl_title);
-CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks
(pl_namespace,pl_title,pl_from);
+CREATE INDEX /*i*/pl_namespace ON /*_*/pagelinks
(pl_namespace,pl_title,pl_from);
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks
(pl_namespace,pl_title,pl_from_namespace,pl_from);
--
@@ -489,6 +492,8 @@
CREATE TABLE /*_*/templatelinks (
-- Key to the page_id of the page containing the link.
tl_from int unsigned NOT NULL default 0,
+ -- Namespace for this page
+ tl_from_namespace int NOT NULL default 0,
-- Key to page_namespace/page_title of the target page.
-- The target page may or may not exist, and due to renames
@@ -499,7 +504,8 @@
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks
(tl_from,tl_namespace,tl_title);
-CREATE UNIQUE INDEX /*i*/tl_namespace ON /*_*/templatelinks
(tl_namespace,tl_title,tl_from);
+CREATE INDEX /*i*/tl_namespace ON /*_*/templatelinks
(tl_namespace,tl_title,tl_from);
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks
(tl_namespace,tl_title,tl_from_namespace,tl_from);
--
@@ -510,6 +516,8 @@
CREATE TABLE /*_*/imagelinks (
-- Key to page_id of the page containing the image / media link.
il_from int unsigned NOT NULL default 0,
+ -- Namespace for this page
+ il_from_namespace int NOT NULL default 0,
-- Filename of target image.
-- This is also the page_title of the file's description page;
@@ -518,7 +526,8 @@
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
-CREATE UNIQUE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
+CREATE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks
(il_to,il_from_namespace,il_from);
--
--
To view, visit https://gerrit.wikimedia.org/r/117373
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Icca99b6ae0ef76cb77695faf82c615516191da36
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Aaron Schulz <asch...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
