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.