https://bugzilla.wikimedia.org/show_bug.cgi?id=3311
Tyler Romeo <tylerro...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |tylerro...@gmail.com
--- Comment #66 from Tyler Romeo <tylerro...@gmail.com> ---
The more you know...
The current query for getting category members is:
SELECT ...
FROM `page`
INNER JOIN `categorylinks`
FORCE INDEX (cl_sortkey)
ON ((cl_from = page_id))
LEFT JOIN `category`
ON ((cat_title = page_title) AND page_namespace = '14')
WHERE cl_to = 'Test' AND cl_type = 'page'
ORDER BY cl_sortkey
LIMIT 201
And, true enough, if you change the cl_to check from a comparison to an IN
operator, it triggers a filesort. *However*, if you instead move the contents
of the WHERE clause into the INNER JOIN condition, then the filesort
disappears. The resulting query is:
SELECT ...
FROM `page`
INNER JOIN `categorylinks`
FORCE INDEX (cl_sortkey)
ON ((cl_from = page_id) AND (cl_to IN ('Test')) AND (cl_type = 'page'))
LEFT JOIN `category`
ON ((cat_title = page_title) AND page_namespace = '14')
ORDER BY cl_sortkey
LIMIT 201
Now I'm not too much of an expert on databases, but theoretically this should
produce the exact same results (since it's an INNER JOIN) but still be
efficient (because the cl_sortkey index includes the cl_from and cl_to
columns).
This would eliminate the need for any new columns and whatnot.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
