On Wed, Feb 28, 2018 at 5:07 PM, Jaime Crespo <jcrespo(a)wikimedia.org> wrote:
On Wed, Feb 28, 2018 at 5:26 PM, Brad Jorsch (Anomie)
<bjorsch(a)wikimedia.org
wrote:
> 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?
>
Actually, MySQL/MariaDB is the *last* database to conform to the sql:1999
WITH standard:
https://modern-sql.com/feature/with#compatibility Even
sqlite suported a limited set of those!
The good news is that, probably because it arrived last, it got a pretty
feature-full implementation:
https://twitter.com/MarkusWinand/status/852862475699707904
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?
Needs more testing-- that query worked for me well enough on my laptop to
expose it directly on a webrequest (<0.1 s), but I only imported the
categorylinks and page tables, so I was working with memory. Obviously, the
more complex the query, and the more results it returns, the less likely it
is to be able to be exposed to, e.g. a public API. But at least there are
configurable limits on recursivitiy and max execution time.
Honestly, given it is a new feature, I don't expect mediawiki --which at
the moment has to support 5.5- to embrace it any time soon. However, I
wanted to ask if it was interesting enough to setup some test hosts for
mediawiki to "play" with it --e.g. evaluate performance--, and maybe (?)
some upgraded mariadb/mysql servers for WMF labsdb (for long-running
analytics or gadgets that generates reports).
I certainly think labsdb users would welcome this feature. Traversing
category trees (Or sometimes even the graph of page links) is
certainly a common thing that people want to do.
--
Brian