On Thu, Feb 21, 2008 at 11:11 AM, Roan Kattouw <roan.kattouw(a)home.nl> wrote:
Although we may want to add cl_is_category,
retrieving subcategories
only is already possible with the current schema:
SELECT whatever FROM categorylinks JOIN page ON page_id=cl_from AND
page_namespace=14 WHERE cl_title='Foo' AND cl_namespace=0;
No idea how efficient this query is, maybe it's evil and uses 57
filesorts, who knows ;)
No filesorts, since nothing has to be sorted (no ORDER BY or GROUP
BY). It will just scan every single categorylinks row corresponding
to the given category. It won't be able to use an index for the
entire query, either: it will have to hit the page table data blocks.
Probably it will take 30 seconds or more on Wikimedia servers for
Living people or other large categories, given that just scanning the
index for all those rows (no joins, no hitting data blocks) is five
seconds. Could be a few minutes, I guess. Either way it's not
remotely acceptable.
That such things are possible to do with the current database is
obvious. After all, we store all the relevant information: there's
got to be some way to retrieve it, somehow, even if it requires
querying all tables in their entirety and manually processing every
row in PHP. So the only real question in these things is efficiency.