MySQL Forums
Forum List  »  Performance

Help optimizing a query
Posted by: m p
Date: October 22, 2004 08:56AM

Hi,
I have a query that takes way too long. I added the indexes that I felt were necessary. Now, I am wondering what else I can do!

Here is the query:

select adresse from tblcourriel where id in
(
select reference from tblcontactinfo where id and type = 2 in
(
select contactInfo from tblcontactcontactinfo where contact in
(
select participant from tblinscription where activite in
(
select id from tblactivite where nom like '%g05%'
)
)
)
)

I give you the explain as well:

1 PRIMARY tblcourriel ALL 2393 Using where
2 DEPENDENT SUBQUERY tblcontactinfo index_subquery Index_3 Index_3 2 func 2 Using index; Using where
3 DEPENDENT SUBQUERY tblcontactcontactinfo index PRIMARY 8 4652 Using where; Using index
4 DEPENDENT SUBQUERY tblinscription index_subquery Index_2 Index_2 4 func 256 Using index; Using where
5 DEPENDENT SUBQUERY tblactivite unique_subquery PRIMARY PRIMARY 2 func 1 Using index; Using where

and the tables definition:

CREATE TABLE `tblcourriel` (
`id` smallint(6) unsigned NOT NULL auto_increment,
`type` smallint(6) unsigned NOT NULL default '0',
`adresse` varchar(150) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `Index_2` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblcontactinfo` (
`id` int(11) unsigned NOT NULL auto_increment,
`type` smallint(6) unsigned NOT NULL default '0',
`reference` smallint(6) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `Index_2` (`type`),
KEY `Index_3` (`reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblcontactcontactinfo` (
`contact` int(11) unsigned NOT NULL default '0',
`contactInfo` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`contact`,`contactInfo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `tblinscription` (
`id` int(11) unsigned NOT NULL auto_increment,
`activite` smallint(6) unsigned NOT NULL default '0',
`participant` int(11) NOT NULL default '0',
`interet` text,
`statut` smallint(6) unsigned default NULL,
`prix` decimal(9,2) default NULL,
PRIMARY KEY (`id`),
KEY `Index_2` (`participant`),
KEY `Index_3` (`activite`),
KEY `Index_4` (`statut`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblactivite` (
`id` smallint(6) unsigned NOT NULL auto_increment,
`nom` varchar(255) NOT NULL default '',
`description` text,
`dateDebut` date default NULL,
`dateFin` date default NULL,
`endroit` smallint(6) default NULL,
`numParticipants` smallint(6) default NULL,
PRIMARY KEY (`id`),
KEY `Index_2` (`endroit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

So, am I missing a critical index or something else? (the query take a VERY long time to complete, in localhost with a 3.2Ghz with 512MB RAM).

Thanks in advance. If you have any idea how to improve it, please post it, maybe it will give me or other another idea. If you need any other information, ask and I will post it.

Mathieu

Options: ReplyQuote


Subject
Views
Written By
Posted
Help optimizing a query
3507
m p
October 22, 2004 08:56AM
2417
October 25, 2004 10:50AM
2334
m p
November 03, 2004 12:46PM


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.