A few hours ago I issued the following command in commonswiki_p
mysql> select * from logging where log_namespace=6 and log_title='Estatuas_y_fuentes_de_La_Granja_de_San_Ildefonso_1.jpg' limit 10; Empty set (1 hour 5 min 33.53 sec)
Why does it take so long? There should be an index on it, which could be used to resolve the whole query as empty:
CREATE INDEX /*i*/page_time ON /*_*/logging (log_namespace,
log_title, log_timestamp);
If we explain the select
mysql> explain select * from logging where log_namespace=6 and log_title='Estatuas_y_fuentes_de_La_Granja_de_San_Ildefonso_1.jpg' limit 10; +----+-------------+---------+------+---------------+-----------+---------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-----------+---------+-------+----------+-------------+ | 1 | SIMPLE | logging | ref | page_time | page_time | 4 | const | 13607245 | Using where | +----+-------------+---------+------+---------------+-----------+---------+-------+----------+-------------+ 1 row in set (0.00 sec)
it does show the index, but the key_len is only 4. It seems it is using page_time only for the log_namespace, and not for the log_title, so it needs to scan 13607245. Compare that with my local server, where that query provides a key_len of 261.
Is the index set correctly? Why is mysql not taking log_title from the index into account? I first thought that perhaps the master wasn't using a full index, and that's what the toolserver replicated, but the index in the master looks complete: http://pastebin.com/vjyjZ7Y2
I also tried fetching the page_id from page using page_namespace and page_title, which is fast, and then searching logging using log_page (indexed by log_page_id_time), but page_id is missing from the view.