On Sun, Sep 7, 2014 at 5:58 AM, Brad Jorsch (Anomie) <bjorsch@wikimedia.org> wrote:

The database query for that is simple enough:

SELECT /* ApiQueryCategoryMembers::run Anomie */  cl_from,cl_sortkey,cl_type,page_namespace,page_title,cl_timestamp  FROM `page`,`categorylinks`  FORCE INDEX (cl_timestamp) WHERE cl_to = 'Copy_to_Wikimedia_Commons_(bot-assessed)' AND (cl_from=page_id)  ORDER BY cl_timestamp,cl_from LIMIT 501;

And the PHP code doesn't do anything complicated either. Maybe Sean can give us more insight if there's some subtle database thing going on here.

As Nik noted, the query plan walking cl_timestamp is not ideal. Plus, even with the forced index the query requires a filesort since cl_timestamp index is on (cl_to,cl_timestamp) and not (cl_timestamp,cl_from).

When I EXPLAIN this query against enwiki, there's no filesort on master, db1055, db1051, and db1066.

   stdClass Object
   (
       [id] => 1
       [select_type] => SIMPLE
       [table] => categorylinks
       [type] => ref
       [possible_keys] => cl_timestamp
       [key] => cl_timestamp
       [key_len] => 257
       [ref] => const
       [rows] => 635858
       [Extra] => Using index condition; Using where
   )
   stdClass Object
   (
       [id] => 1
       [select_type] => SIMPLE
       [table] => page
       [type] => eq_ref
       [possible_keys] => PRIMARY
       [key] => PRIMARY
       [key_len] => 4
       [ref] => enwiki.categorylinks.cl_from
       [rows] => 1
       [Extra] =>
   )

There is on db1061, db1062, db1065, db1072, and db1073.

   stdClass Object
   (
       [id] => 1
       [select_type] => SIMPLE
       [table] => categorylinks
       [type] => ref
       [possible_keys] => cl_timestamp
       [key] => cl_timestamp
       [key_len] => 257
       [ref] => const
       [rows] => 706656
       [Extra] => Using index condition; Using where; Using filesort
   )
   stdClass Object
   (
       [id] => 1
       [select_type] => SIMPLE
       [table] => page
       [type] => eq_ref
       [possible_keys] => PRIMARY
       [key] => PRIMARY
       [key_len] => 4
       [ref] => enwiki.categorylinks.cl_from
       [rows] => 1
       [Extra] =>
   )

My wild guess would be that the latter set of databases were somehow created differently so that InnoDB is clustering using some index other than cl_from.

 

Removing the FORCE INDEX would allow cl_sortkey index to be used, with better selectivity.