Help optimizing a query

[m p]

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