MySQL Forums :: Database Design & Data Modelling :: Slow query - help me optimize ?


Advanced Search

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 03/22/2009 07:44AM
Re: Slow query - help me optimize ? Rick James 03/23/2009 11:31PM
Re: Slow query - help me optimize ? D H 03/24/2009 12:21PM
Re: Slow query - help me optimize ? D H 03/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.