https://bugzilla.wikimedia.org/show_bug.cgi?id=62620

            Bug ID: 62620
           Summary: Add index on revision user data for contributions
                    lookup
           Product: Wikimedia
           Version: unspecified
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: Unprioritized
         Component: General/Unknown
          Assignee: wikibugs-l@lists.wikimedia.org
          Reporter: ebernhard...@wikimedia.org
       Web browser: ---
   Mobile Platform: ---

Short description:

Add an index for answering contributions queries.

Example queries:

All queries below may also have a condition of

    rev_id > :someBinaryUid
or
    rev_id < :someBinaryUid

for pagination

The FROM/JOIN statement can also switch to:

    FROM flow_revision
    INNER JOIN flow_header_revision ON header_rev_id = rev_id
    INNER JOIN flow_workflow ON workflow_id = header_workflow_id

SELECT *
  FROM flow_revision
  INNER JOIN flow_tree_revision ON tree_rev_id = rev_id
  INNER JOIN tree_descendant_id = tree_rev_descendant_id
  INNER JOIN flow_workflow ON workflow_id = tree_ancestor_id
 WHERE rev_user_id > :someId
   AND rev_user_ip IS NULL 
   AND rev_user_wiki = :someWiki
   AND workflow-wiki = :someWiki
 ORDER BY rev_id DESC
 LIMIT :limit

SELECT *
  FROM flow_revision
  INNER JOIN flow_tree_revision ON tree_rev_id = rev_id
  INNER JOIN tree_descendant_id = tree_rev_descendant_id
  INNER JOIN flow_workflow ON workflow_id = tree_ancestor_id
 WHERE rev_user_wiki = :someWiki
   AND rev_user_id = :someId
   AND rev_user_ip IS NULL;
 ORDER BY rev_id DESC
 LIMIT :limit

SELECT *
  FROM flow_revision
  INNER JOIN flow_tree_revision ON tree_rev_id = rev_id
  INNER JOIN tree_descendant_id = tree_rev_descendant_id
  INNER JOIN flow_workflow ON workflow_id = tree_ancestor_id
 WHERE rev_user_wiki = :someWiki
   AND rev_user_id IS NULL
   AND rev_user_ip = :someIp;
 ORDER BY rev_id DESC
 LIMIT :limit

We are aware these queries are well less than efficient, to many joins
especially for the ones that join against flow_tree_revision.  We are revisting
the data modeling to figure out how more efficiently model the data.

Which wikis are affected:

flowdb on the external(non-wiki) db cluster

Which tables:

flow_revision

What is the change to those tables:

CREATE INDEX /*i*/flow_revision_user 
    ON /*_*/flow_revision (rev_user_id, rev_user_ip, rev_user_wiki);

Links to gerrit changes and/or other related bug reports.

https://gerrit.wikimedia.org/r/#/c/116115/

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l

Reply via email to