MySQL Forums
Forum List  »  Optimizer & Parser

JOIN query too slow
Posted by: N. Kovac
Date: October 13, 2012 11:52AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
JOIN query too slow
2664
October 13, 2012 11:52AM
1328
October 14, 2012 10:26AM
1379
October 16, 2012 07:48PM


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.