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

Options: ReplyQuote


Subject
Written By
Posted
Slow query - help me optimize ?
D H
March 22, 2009 07:44AM
D H
March 24, 2009 12:21PM
D H
March 25, 2009 06:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.