On Wed, Feb 28, 2018 at 8:47 AM, Jaime Crespo <jcrespo(a)wikimedia.org> wrote:
Very recently I have been experimenting with recursive
Common Table
Expressions [2], which are or will be available on the latest versions of
MySQL and MariaDB.
Do the other databases MediaWiki tries to support have that feature?
With a single query on can obtain all titles directly
or indirectly in a
category:
WITH RECURSIVE cte (cl_from, cl_type) AS
(
SELECT cl_from, cl_type FROM categorylinks WHERE cl_to =
'Database_management_systems' -- starting category
UNION
SELECT categorylinks.cl_from, categorylinks.cl_type FROM cte JOIN page
ON
cl_from = page_id JOIN categorylinks ON page_title = cl_to WHERE
cte.cl_type
= 'subcat' -- subcat addition on each iteration
)
SELECT page_title FROM cte JOIN page ON cl_from = page_id WHERE
page_namespace = 0 ORDER BY page_title; -- printing only articles in the
end
, ordered by title
Does that work efficiently on huge categories, or does it wind up fetching
millions of rows and filesorting?
--
Brad Jorsch (Anomie)
Senior Software Engineer
Wikimedia Foundation