Page MenuHomePhabricator

Make page_len index actually useful so Special:Longpages and Special:Shortpages will be efficient
Closed, ResolvedPublic

Description

Presumably in an attempt to optimize Special:(Long|Short)pages, a page_len index was introduced in version 1.5 (according to a comment I found). However, because the query these special pages do is something like SELECT stuff FROM page WHERE page_namespace IN (content namespaces) AND page_is_redirect=0 ORDER BY page_len (ASC|DESC) , an index on page_len alone doesn't suffice.

For Longpages it's arguably good enough, since the WHERE clause is gonna be true for almost all of the 50/100/500/whatever longest pages. For Shortpages, however, the opposite is true.

In order to fix this properly, we'd have to extend the index of (page_len) to cover (page_len, page_is_redirect, ppage_namespace) so the WHERE clause is fully indexed. Any existing legitimate uses of the page_len index (are there any?) will continue to work because page_len will still be the first field in the index.

I'll attach a patch shortly.


Version: 1.18.x
Severity: enhancement

Details

Reference
bz26393

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 11:13 PM
bzimport set Reference to bz26393.
bzimport added a subscriber: Unknown Object (MLST).

(In reply to comment #0)

In order to fix this properly, we'd have to extend the index of (page_len) to
cover (page_len, page_is_redirect, ppage_namespace)

Strike that, should be (page_is_redirect, page_namespace, page_len)

Any existing legitimate uses of the page_len index (are there
any?) will continue to work because page_len will still be the first field in
the index.

...which means this isn't true.

Created attachment 7922
Proposed patch

The attached patch adds a page_redirect_namespace_len index on (page_is_redirect, page_namespace, page_len) and modifies Special:Shortpages (and by extension Special:Longpages) to use it. It also makes these special pages just query the main namespace rather than all content namespaces, or the query will still be potentially unindexed.

I didn't remove the page_len index in this patch, even though it seems to be unused with these changes applied.

Attached:

*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*

sumanah wrote:

Committed in r102785 .

FIXED. Patch Applied, per comment 4.