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/
Version: unspecified
Severity: normal