On Tue, Apr 8, 2008 at 5:18 AM, <aaron(a)svn.wikimedia.org> wrote:
> Log Message:
> -----------
> Tweak - title index better than user in these cases most likely
> # Don't use the wrong logging index
> - if( $this->user ) {
> + if( $this->title || $this->pattern ) {
> + $index = array( 'USE INDEX' => array( 'logging' => 'page_time' ) );
> + } else if( $this->user ) {
> $index = array( 'USE INDEX' => array( 'logging' => 'user_time' ) );
> - } else if( $this->title || $this->pattern ) {
> - $index = array( 'USE INDEX' => array( 'logging' => 'page_time' ) );
In many cases that won't be the case, and the optimizer should be able to tell:
mysql> EXPLAIN SELECT
log_type,log_action,log_user,log_namespace,log_title,log_params,log_comment,0
AS log_id,log_timestamp,user_name FROM `logging`,`user` WHERE
(log_type NOT IN('suppress')) AND log_user = '1' AND log_namespace =
'0' AND log_title = 'Main_Page' AND (user_id = log_user) ORDER BY
log_timestamp DESC LIMIT 51;
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | logging | ref | type_time,user_time,page_time
| page_time | 261 | const,const | 66 | Using where |
| 1 | SIMPLE | user | eq_ref | PRIMARY
| PRIMARY | 4 | enwiki.logging.log_user | 1 | |
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
2 rows in set (0.05 sec)
mysql> EXPLAIN SELECT
log_type,log_action,log_user,log_namespace,log_title,log_params,log_comment,0
AS log_id,log_timestamp,user_name FROM `logging`,`user` WHERE
(log_type NOT IN('suppress')) AND log_user = '1732' AND log_namespace
= '0' AND log_title = 'Main_Page' AND (user_id = log_user) ORDER BY
log_timestamp DESC LIMIT 51;
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | logging | ref | type_time,user_time,page_time
| user_time | 4 | const | 1 | Using where |
| 1 | SIMPLE | user | eq_ref | PRIMARY
| PRIMARY | 4 | enwiki.logging.log_user | 1 | |
+----+-------------+---------+--------+-------------------------------+-----------+---------+-------------------------+------+-------------+
2 rows in set (0.17 sec)
Granted that that's on MySQL 5 (toolserver), not 4, but you don't need
to *always* force the index. Just make sure it's ignoring conditions
like log_type NOT IN ('suppress') by forcing times in those cases so
it avoids a filesort, and it should make a good choice. Or do you
know of further scenarios where it doesn't?
