Slow query - help me optimize ?
Posted by:
D H
Date: March 22, 2009 07:44AM
Hello,
Using MySQL 4.1.11 (cannot upgrade at this point).
I have got these two tables:
CREATE TABLE `gu_user` (
`id` bigint(20) unsigned NOT NULL default '0',
`classement` bigint(20) unsigned default '18446744073709551615',
.
.
.
PRIMARY KEY (`id`),
KEY `classement` (`classement`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `gu_gang` (
`id` bigint(20) NOT NULL auto_increment,
`user_id1` bigint(20) default NULL,
`user_id2` bigint(20) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users` (`user_id1`,`user_id2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=37305 ;
I am running this type of queries, and they show in the slow queries log:
# Time: 090322 14:33:44
# User@Host: xxxxxxx[xxxxxxxx] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 5 Rows_examined: 9158
SELECT gu_user.id AS id, cur_level, pseudo, life, life_max, gang_size FROM gu_gang, gu_user WHERE ((user_id1=1177486746 AND user_id2=gu_user.id) OR (user_id2=1177486746 AND user_id1=gu_user.id)) ORDER BY classement ASC LIMIT 0, 5;
An explain gives this output:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gu_user ALL PRIMARY NULL NULL NULL 8783 Using temporary; Using filesort
1 SIMPLE gu_gang index users users 18 NULL 37144 Using where; Using index
I believe the problem to come mostly from the table gu_user, for which its creating a temporary table and examining all rows (?).
I cannot figure how to optimize indexes or the query, so the query runs faster. Help appreciated.
Thanks