*_userindex tables need to be documented
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Ireas
	Sep 13 2013, 7:24 PM

Description

Some SQL queries on the replica tables take much longer than on the Toolserver. For example, this query for the first edit of a user takes 0.01 sec on the Toolserver and 49.08 sec on Tool Labs (for dewiki_p):

select rev_timestamp from revision where rev_user=336793 order by rev_timestamp asc limit 1;

This is critical for tools as stimmberechtigung that checks the right to vote of a user in the German Wikipedia (Toolserver: toolserver.org/~stimmberechtigung/, Labs: tools.wmflabs.org/stimmberechtigung/). While it runs very fast on the Toolserver, it takes several minutes to load it on Labs.

Version: unspecified
Severity: normal

Details

Reference: bz54107

Event Timeline

• bzimport raised the priority of this task from to Medium.Nov 22 2014, 2:14 AM

• bzimport added a project: Toolforge.

• bzimport set Reference to bz54107.

Ireas created this task.Sep 13 2013, 7:24 PM

For queries that select on rev_user, there is the table "revision_userindex":

MariaDB [dewiki_p]> select rev_timestamp from revision_userindex where rev_user=336793 order by rev_timestamp asc limit 1;
+----------------+
	rev_timestamp
+----------------+
	20070115154431
+----------------+
1 row in set (0.05 sec)

MariaDB [dewiki_p]>

Unfortunately, it isn't documented at [[wikitech:Nova Resource:Tools/Help]] (so far :-)). I'll leave that to Coren as my knowledge of what and when is purely based on word of mouth.

Hi Tim, thanks! This helps a lot. My tool is now working fine. :) Of course, it would be great to have this documented for further use.

warnckew wrote:

I've added a first pass on documenting this to the Tool Labs help, see https://wikitech.wikimedia.org/wiki/Nova_Resource:Tools/Help#Tables_for_revision_or_logging_queries_involving_user_names_and_IDs

The canonical source of information for what "special" views exists is the source of the maintenance script:

https://git.wikimedia.org/blob/operations%2Fsoftware/HEAD/maintain-replicas%2Fmaintain-replicas.pl

Where the definition of the views themselves can be found.

Has been documented since.

*_userindex tables need to be documentedClosed, ResolvedPublicActions

Description

Details

Event Timeline

*_userindex tables need to be documented
Closed, ResolvedPublic
Actions