On Tue, Apr 8, 2008 at 5:18 AM, aaron@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?