On Wed, Feb 28, 2018 at 8:47 AM, Jaime Crespo jcrespo@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?