JOIN query too slow

[N. Kovac]

Hi all!

I'm trying to optimize some of my queries, as this database I'm working on is getting big.

I've a table with this structure:

mysql> SHOW CREATE TABLE Personas_experiencias;
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Personas_experiencias | CREATE TABLE `Personas_experiencias` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`IdPersona` int(11) NOT NULL,
`Empresa` varchar(150) COLLATE latin1_spanish_ci NOT NULL,
`Puesto` varchar(4) COLLATE latin1_spanish_ci NOT NULL,
`Funcion` varchar(150) COLLATE latin1_spanish_ci DEFAULT NULL,
`Inicio` date DEFAULT NULL,
`Fin` date DEFAULT NULL,
`Actual` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `IdPersona` (`IdPersona`)
) ENGINE=MyISAM AUTO_INCREMENT=5728 DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci |
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

The thing is I need to do many JOINs on this table over itself, in order to look for several parameters in the same query. So, the basic query I'm trying to optimize is this:

SELECT `Personas`.`IdPersona` AS `id`,`Personas`.`NIF` AS `DNICIF`,`Personas`.`Nombre` AS `Nombre`,`Personas`.`Apellidos` AS `Apellidos`,`Personas`.`Email` AS `Email`,`Personas`.`Telefono` AS `OtrosDatos`
FROM `Personas`, `Personas_experiencias` AS `Personas_experiencias0`, `Personas_experiencias` AS `Personas_experiencias1`, `Personas_experiencias` AS `Personas_experiencias2`, `Personas_experiencias` AS `Personas_experiencias3`
WHERE (`Personas`.`IdPersona` = `Personas_experiencias0`.`IdPersona`) AND (`Personas_experiencias0`.`IdPersona` = `Personas_experiencias1`.`IdPersona`) AND (`Personas_experiencias1`.`IdPersona` = `Personas_experiencias2`.`IdPersona`) AND (`Personas_experiencias2`.`IdPersona` = `Personas_experiencias3`.`IdPersona`);

Well, the EXPLAIN for this SELECT is the following:

mysql> EXPLAIN SELECT `Personas`.`IdPersona` AS `id`,`Personas`.`NIF` AS `DNICIF`,`Personas`.`Nombre` AS `Nombre`,`Personas`.`Apellidos` AS `Apellidos`,`Personas`.`Email` AS `Email`,`Personas`.`Telefono` AS `OtrosDatos` FROM `Personas`, `Personas_experiencias` AS `Personas_experiencias0`, `Personas_experiencias` AS `Personas_experiencias1`, `Personas_experiencias` AS `Personas_experiencias2`, `Personas_experiencias` AS `Personas_experiencias3` WHERE (`Personas`.`IdPersona` = `Personas_experiencias0`.`IdPersona`) AND (`Personas_experiencias0`.`IdPersona` = `Personas_experiencias1`.`IdPersona`) AND (`Personas_experiencias1`.`IdPersona` = `Personas_experiencias2`.`IdPersona`) AND (`Personas_experiencias2`.`IdPersona` = `Personas_experiencias3`.`IdPersona`);
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
| 1 | SIMPLE | Personas_experiencias0 | index | IdPersona | IdPersona | 4 | NULL | 4980 | Using index |
| 1 | SIMPLE | Personas | eq_ref | PRIMARY | PRIMARY | 4 | qmn125.Personas_experiencias0.IdPersona | 1 | |
| 1 | SIMPLE | Personas_experiencias1 | ref | IdPersona | IdPersona | 4 | qmn125.Personas_experiencias0.IdPersona | 5 | Using index |
| 1 | SIMPLE | Personas_experiencias2 | ref | IdPersona | IdPersona | 4 | qmn125.Personas_experiencias1.IdPersona | 5 | Using where; Using index |
| 1 | SIMPLE | Personas_experiencias3 | ref | IdPersona | IdPersona | 4 | qmn125.Personas.IdPersona | 5 | Using where; Using index |
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
5 rows in set (0.03 sec)

4980 rows are the total number of rows at Personas_experiencias. I think the main problem is the first row of the explain command, and it takes up to 1 minute and 30 seconds to execute (I'd need to reduce it to at least to the 1/3 of time!).

Can someone advise how to improve that? I tried to define several indexes involving several columns but I can't find a way to reduce this!

Thanks so much in advance!