r83993 MediaWiki - Code Review archive

Why do you think the filesort would be <500 entries? Wouldn't it have to sort the whole category?

Also, you're going to be breaking this for anyone who did the schema update. Granted, this should only be development sites.

But it also possibly affects Translate Wiki, who do run trunk, and are likely to have upgraded. This then breaks it completely for them....

Also, the API is now doing unindexed queries...

I strongly suggest this be reverted.

Removing the ordering on cl_type makes the rest of the ordering unindexed (in the general case at least; it's fine if cmtype is set to a single value, because ordering by cl_type does nothing in that case, but this isn't the default) and, as Tim points out, that means the entire category has to be sorted to get the first 50 (or 500 or whatever) entries. We have categories with hundreds of thousands of members on enwiki (largest is 800k IIRC) and AFAICT this is going to totally explode when confronted with such categories.

Ok, I see your point.

I looked at query execution plans on some smaller wikis and on the toolserver, and indeed there is too much risk.

I looked also at the alternative solution, which works much better - converting cl_type to int and using a small auxiliary name-value table to join when mapping from the text value is needed.

Here is a sample query using the auxiliary table:

mysql> EXPLAIN SELECT           
  cl_from,page_namespace,page_title,page_id,cl_sortkey,cl_timestamp   
FROM `page`,`categorylinks`,catlinks_types  
WHERE cl_to = 'Loga' AND ctl_id=cl_type AND
      ctl_value IN ('page','subcat','file') 
 AND (cl_from >= '1474') AND (cl_from=page_id)   ORDER BY cl_type, cl_sortkey, cl_from LIMIT 51;
+----+-------------+----------------+--------+-------------------------------------------+------------+---------+-------------------------------------------------+-----------------------------------------------------------+
| id | select_type | table          | type   | possible_keys                             | key        | key_len | ref                                       | rows | Extra                                                     |
+----+-------------+----------------+--------+-------------------------------------------+------------+---------+-------------------------------------------------+-----------------------------------------------------------+
|  1 | SIMPLE      | catlinks_types | index  | cl_types                                  | cl_types   | 22      | NULL                                      |    3 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | categorylinks  | ref    | cl_from,cl_sortkey,cl_timestamp,ctl_types | cl_sortkey | 771     | const,plwikimedia_p.catlinks_types.ctl_id |    5 | Using where                                               |
|  1 | SIMPLE      | page           | eq_ref | PRIMARY                                   | PRIMARY    | 4       | plwikimedia_p.categorylinks.cl_from       |    1 |                                                           |
+----+-------------+----------------+--------+-------------------------------------------+------------+---------+-------------------------------------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

I think this is much better.

But then, it seems like that cl_type is directly derived from cl_from and and its namespace. I look at its usage and except for picking up subcategories and files first in CategoryPage (and few extensions doing similar stuff). As those join the page table anyway, using namespace from there or the immediate table looks much better for me. Do we really need cl_type at all? What do you think?

Yes, we need cl_type. It's true that the information comes directly from the page table, but that's the exact problem: it's in another table. So you can't put page_namespace and cl_sortkey in one index, so you can't order by it or efficiently filter by it while also doing ordering/filtering based on fields in categorylinks. Try rewriting the query above to use, say, page_namespace=6 instead of cl_type='fi;e' and you'll see nasty filesorts or table scans happen.

Why is making cl_type numeric so much better? Your paste shows that the relevant query filesorts and uses a temporary table.

In an ideal world it would've been numeric from the start, but making it a varchar makes migration trivial, and the difference in space requirements is negligible (a few bytes per row, at most, for rows that can be up to 700+ bytes each).