On Wed, Feb 28, 2018 at 8:47 AM, Jaime Crespo <jcre...@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 _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
