[Labs-l] Database queries slower here than in the Toolserver

[Marc A. Pelletier]

On 09/06/2013 01:36 PM, Pietro De Nicolao wrote:
>> SELECT page_namespace, page_title
>> 	FROM revision
>> 	JOIN page ON rev_page=page_id
>> 	WHERE rev_user_text="Pietrodn";

Hm.  I need to document this.  The view "revision" does not have indices
on rev_user and rev_user_text because some of those values are
conditionally nulled when suppressed; the alternative view
"revision_userindex" removes the rows entierly, but has indices.  The
equivalent:

> SELECT page_namespace, page_title
> 	FROM revision_userindex
> 	JOIN page ON rev_page=page_id
> 	WHERE rev_user_text="Pietrodn";

Takes:
> real    0m0.155s
> user    0m0.004s
> sys     0m0.004s

The rule is simple:  if you have a where clause on rev_user or
rev_user_text, user revison_userindex for geat justice.

There is also the equivalent logging_userindex for the logging table.

-- Marc