On Tue, Apr 8, 2008 at 11:50 AM, aaron@svn.wikimedia.org wrote:
Log Message:
- Let MySQL choose among more indexes for title/user conditions # Don't use the wrong logging index
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' ) );
if( $this->title || $this->pattern || $this->user ) {$index = array( 'USE INDEX' => array( 'logging' => array('page_time','user_time') ) ); } else if( $this->type ) { $index = array( 'USE INDEX' => array( 'logging' => 'type_time' ) ); } else {
You're still making the assumption that the only indexes are page_time, user_time, type_time, times. There's no justification for that; third-party sysadmins may add custom indexes. Wikimedia has some, in fact, for watchlists at least. IMO, you should only use USE INDEX or FORCE INDEX at all when you *know* there's a specific problem with how some versions of MySQL choose indexes. Otherwise it's pointless at best, and possibly harmful.
I don't see any reason to force an index in any case here except when there's a condition on log_type of the form log_type != 'x' or log_type NOT IN ('x', 'y', ...), and no other conditions on log_type. In that case, IGNORE INDEX type_time would probably be helpful.
The are needed to avoid having the site go down like a did for a few minutes without it. It was starting with the user table and then doing a huge filesorts.
Simetrical-3 wrote:
On Tue, Apr 8, 2008 at 11:50 AM, aaron@svn.wikimedia.org wrote:
Log Message:
- Let MySQL choose among more indexes for title/user conditions # Don't use the wrong logging index
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' ) );
if( $this->title || $this->pattern || $this->user ) {$index = array( 'USE INDEX' => array( 'logging'=> array('page_time','user_time') ) ); } else if( $this->type ) { $index = array( 'USE INDEX' => array( 'logging' => 'type_time' ) ); } else {
You're still making the assumption that the only indexes are page_time, user_time, type_time, times. There's no justification for that; third-party sysadmins may add custom indexes. Wikimedia has some, in fact, for watchlists at least. IMO, you should only use USE INDEX or FORCE INDEX at all when you *know* there's a specific problem with how some versions of MySQL choose indexes. Otherwise it's pointless at best, and possibly harmful.
I don't see any reason to force an index in any case here except when there's a condition on log_type of the form log_type != 'x' or log_type NOT IN ('x', 'y', ...), and no other conditions on log_type. In that case, IGNORE INDEX type_time would probably be helpful.
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
wikitech-l@lists.wikimedia.org