I'm trying to find out how to optimize our mysql server because for some reason this request takes 10 minutes to complete and all the time it stays on status "Copying to tmp table" :
SELECT count(val_varchar_1) as le_nb_occurences, val_varchar_1 AS intitule
FROM my_base_fiche_valeur
WHERE 1
AND id_my_base=1
AND id_my_base_lien_base_champ=45
AND val_varchar_1!=''
AND my_base_fiche_valeur.id_my_base_fiche IN (
SELECT distinct my_base_fiche.id_my_base_fiche
FROM my_base_fiche
LEFT JOIN my_base_fiche_valeur
ON my_base_fiche_valeur.id_my_base_fiche=my_base_fiche.id_my_base_fiche
WHERE (
my_base_fiche.id_my_base='1' AND (
1 AND (
my_base_fiche.my_base_fiche_visible=1)
AND 1
AND my_base_fiche.id_my_base_fiche IN (
SELECT DISTINCT(id_my_base_fiche)
FROM my_base_fiche_valeur
WHERE my_base_fiche_valeur.id_my_base=1
AND my_base_fiche_valeur.id_my_base_lien_base_champ IN (1,2)
AND (
my_base_fiche_valeur.id_my_base='1'
AND (
(
( val_int_1 LIKE '%valon%'))
OR (
( val_varchar_1 LIKE '%valon%')
) OR (
( val_varchar_2 LIKE '%valon%')
) OR (
( val_varchar_3 LIKE '%valon%')
) OR (
( val_varchar_4 LIKE '%valon%')
) OR (
( val_varchar_5 LIKE '%valon%')
) OR (
( val_varchar_6 LIKE '%valon%')
) OR (
( val_varchar_7 LIKE '%valon%')
) OR (
( val_varchar_8 LIKE '%valon%')
) OR (
( val_longtext_1 LIKE '%valon%')
) OR (
( val_longtext_2 LIKE '%valon%')
)
)
)
)
)
)
AND my_base_fiche_valeur.id_my_base_lien_base_champ=1
ORDER BY val_varchar_1 ASC
)
GROUP BY (val_varchar_1)
ORDER BY intitule;
and here is the explain info :
+----+--------------------+-----------------------+--------+-----------------------------------------+-----------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+--------+-----------------------------------------+-----------+---------+-------+------+----------------------------------------------+
| 1 | PRIMARY | my_base_fiche_valeur | ref | NewIndex2,NewIndex3,NewIndex7 | NewIndex2 | 4 | const | 645 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | my_base_fiche_valeur | ref | NewIndex1,NewIndex2,NewIndex4 | NewIndex2 | 4 | const | 451 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | my_base_fiche | eq_ref | PRIMARY,NewIndex1,NewIndex2,NewIndex3 | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | my_base_fiche_valeur | range | NewIndex1,NewIndex2,NewIndex3,NewIndex4 | NewIndex2 | 4 | NULL | 991 | Using where; Using temporary |
+----+--------------------+-----------------------+--------+-----------------------------------------+-----------+---------+-------+------+----------------------------------------------+
4 rows in set (0.00 sec)
I profiled the query to get more info and now it's looking stranger as the sum of the times is nowhere near the 10 minutes :
mysql> SHOW PROFILE for query 2;
+-------------------------------+----------+
| Status | Duration |
+-------------------------------+----------+
| Sending data | 0.000009 |
| executing | 0.000004 |
| Copying to tmp table | 0.001858 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.001825 |
| Sending data | 0.000009 |
| executing | 0.000004 |
| Copying to tmp table | 0.001825 |
| Sending data | 0.000010 |
| executing | 0.000003 |
| Copying to tmp table | 0.001867 |
| Sending data | 0.000015 |
| executing | 0.000004 |
| Copying to tmp table | 0.001843 |
| Sending data | 0.000011 |
| executing | 0.000004 |
| Copying to tmp table | 0.001895 |
| Sending data | 0.000014 |
| executing | 0.000004 |
| Copying to tmp table | 0.001865 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.001871 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.001828 |
| Sending data | 0.000009 |
| executing | 0.000004 |
| Copying to tmp table | 0.001938 |
| Sending data | 0.000018 |
| executing | 0.000003 |
| Copying to tmp table | 0.002009 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.001966 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.002039 |
| Sending data | 0.000013 |
| executing | 0.000003 |
| Copying to tmp table | 0.001985 |
| Sending data | 0.000012 |
| executing | 0.000004 |
| Copying to tmp table | 0.001973 |
| Sending data | 0.000014 |
| executing | 0.000004 |
| Copying to tmp table | 0.001864 |
| Sending data | 0.000012 |
| executing | 0.000004 |
| Copying to tmp table | 0.001833 |
| Sending data | 0.000010 |
| executing | 0.000004 |
| Copying to tmp table | 0.001846 |
| Sending data | 0.000010 |
| executing | 0.000004 |
| Copying to tmp table | 0.001829 |
| Sending data | 0.000010 |
| executing | 0.000004 |
| Copying to tmp table | 0.001868 |
| Sending data | 0.000013 |
| executing | 0.000003 |
| Copying to tmp table | 0.001858 |
| Sending data | 0.000013 |
| executing | 0.000003 |
| Copying to tmp table | 0.001847 |
| Sending data | 0.000010 |
| executing | 0.000004 |
| Copying to tmp table | 0.001841 |
| Sending data | 0.000013 |
| executing | 0.000004 |
| Copying to tmp table | 0.001828 |
| Sending data | 0.000010 |
| executing | 0.000004 |
| Copying to tmp table | 0.001850 |
| Sending data | 0.000011 |
| executing | 0.000004 |
| Copying to tmp table | 0.001832 |
| Sending data | 0.000012 |
| Sending data | 0.000060 |
| Sorting result | 0.000033 |
| Sending data | 0.000022 |
| end | 0.000005 |
| removing tmp table | 0.000013 |
| end | 0.000005 |
| removing tmp table | 0.000011 |
| end | 0.000005 |
| removing tmp table | 0.000005 |
| end | 0.000011 |
| query end | 0.000006 |
| closing tables | 0.000022 |
| freeing items | 0.000050 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000041 |
| Waiting for query cache lock | 0.000005 |
| freeing items | 0.000005 |
| storing result in query cache | 0.000035 |
| logging slow query | 0.000005 |
| logging slow query | 0.000061 |
| cleaning up | 0.000029 |
+-------------------------------+----------+
100 rows in set (0.00 sec)
I'll be trying to mess with the configuration but if someone understands what's happening it could really help 🙂
It's a Mysql 5.5.37 on debian, here are some of the values in use :
| join_buffer_size | 2097152 |
| max_heap_table_size | 67108864 |
| max_join_size | 18446744073709551615 |
| sort_buffer_size | 2097152 |
| tmp_table_size | 67108864 |
Thanks
Update:
here are the show create tables :
my_base_fiche
Create Table: CREATE TABLE `my_base_fiche` (
`id_my_base_fiche` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_my_base` int(10) unsigned NOT NULL,
`date_ajout` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modif` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`id_user_ajout` int(10) unsigned DEFAULT NULL,
`id_user_modif` int(10) unsigned DEFAULT NULL,
`my_base_fiche_visible` tinyint(1) DEFAULT '1',
`my_base_fiche_internet` tinyint(1) DEFAULT '1',
`my_base_fiche_intranet` tinyint(1) DEFAULT '1',
`my_base_fiche_cmscommentaire` int(1) DEFAULT '0',
`my_base_fiche_cache` longblob,
`my_base_fiche_cache_propre` longblob,
PRIMARY KEY (`id_my_base_fiche`),
KEY `NewIndex1` (`id_my_base`),
KEY `NewIndex2` (`my_base_fiche_visible`),
KEY `NewIndex3` (`id_my_base_fiche`,`id_my_base`),
KEY `NewIndex4` (`id_user_ajout`),
KEY `NewIndex5` (`id_user_modif`),
KEY `NewIndex6` (`date_ajout`),
KEY `NewIndex7` (`date_modif`)
) ENGINE=MyISAM AUTO_INCREMENT=623 DEFAULT CHARSET=latin1
my_base_fiche_valeur:
Create Table: CREATE TABLE `my_base_fiche_valeur` (
`id_my_base_fiche_valeur` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_my_base_fiche` int(10) unsigned NOT NULL,
`id_my_base_lien_base_champ` int(10) unsigned NOT NULL,
`id_my_base` int(10) unsigned NOT NULL,
`val_varchar_1` varchar(255) DEFAULT NULL,
`val_varchar_2` varchar(255) DEFAULT NULL,
`val_varchar_3` varchar(255) DEFAULT NULL,
`val_varchar_4` varchar(255) DEFAULT NULL,
`val_varchar_5` varchar(255) DEFAULT NULL,
`val_varchar_6` varchar(255) DEFAULT NULL,
`val_varchar_7` varchar(255) DEFAULT NULL,
`val_varchar_8` varchar(255) DEFAULT NULL,
`val_text_1` text,
`val_longtext_1` longtext,
`val_longtext_2` longtext,
`val_int_1` int(10) DEFAULT '0',
`val_int_2` int(10) DEFAULT '0',
`val_int_3` int(10) DEFAULT '0',
`val_float_1` float DEFAULT '0',
`val_float_2` float DEFAULT '0',
`val_datetime_1` datetime DEFAULT NULL,
`val_datetime_2` datetime DEFAULT NULL,
`val_date_1` date DEFAULT NULL,
`val_date_2` date DEFAULT NULL,
`date_ajout` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modif` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`id_user_ajout` int(10) unsigned DEFAULT NULL,
`id_user_modif` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id_my_base_fiche_valeur`),
KEY `NewIndex1` (`id_my_base_fiche`),
KEY `NewIndex2` (`id_my_base_lien_base_champ`),
KEY `NewIndex3` (`id_my_base`),
KEY `NewIndex4` (`id_my_base_fiche`,`id_my_base`),
KEY `NewIndex5` (`val_date_1`),
KEY `NewIndex6` (`val_date_2`),
KEY `NewIndex7` (`val_varchar_1`),
KEY `NewIndex8` (`id_my_base_fiche_valeur`,`id_my_base_fiche`,`id_my_base_lien_base_champ`,`id_my_base`)
) ENGINE=MyISAM AUTO_INCREMENT=54010 DEFAULT CHARSET=latin1
SHOW INDEX FROM my_base_fiche;
+----------------+------------+-----------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_base_fiche | 0 | PRIMARY | 1 | id_my_base_fiche | A | 621 | NULL | NULL | | BTREE | | |
| my_base_fiche | 1 | NewIndex1 | 1 | id_my_base | A | 1 | NULL | NULL | | BTREE | | |
| my_base_fiche | 1 | NewIndex2 | 1 | my_base_fiche_visible | A | 1 | NULL | NULL | YES | BTREE | | |
| my_base_fiche | 1 | NewIndex3 | 1 | id_my_base_fiche | A | 621 | NULL | NULL | | BTREE | | |
| my_base_fiche | 1 | NewIndex3 | 2 | id_my_base | A | 621 | NULL | NULL | | BTREE | | |
| my_base_fiche | 1 | NewIndex4 | 1 | id_user_ajout | A | 1 | NULL | NULL | YES | BTREE | | |
| my_base_fiche | 1 | NewIndex5 | 1 | id_user_modif | A | 4 | NULL | NULL | YES | BTREE | | |
| my_base_fiche | 1 | NewIndex6 | 1 | date_ajout | A | 77 | NULL | NULL | | BTREE | | |
| my_base_fiche | 1 | NewIndex7 | 1 | date_modif | A | 621 | NULL | NULL | | BTREE | | |
+----------------+------------+-----------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SHOW INDEX FROM my_base_fiche_valeur;
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_base_fiche_valeur | 0 | PRIMARY | 1 | id_my_base_fiche_valeur | A | 47712 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex1 | 1 | id_my_base_fiche | A | 619 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex2 | 1 | id_my_base_lien_base_champ | A | 76 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex3 | 1 | id_my_base | A | 1 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex4 | 1 | id_my_base_fiche | A | 619 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex4 | 2 | id_my_base | A | 619 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex5 | 1 | val_date_1 | A | 47712 | NULL | NULL | YES | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex6 | 1 | val_date_2 | A | 47712 | NULL | NULL | YES | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex7 | 1 | val_varchar_1 | A | 23856 | NULL | NULL | YES | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex8 | 1 | id_my_base_fiche_valeur | A | 47712 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex8 | 2 | id_my_base_fiche | A | 47712 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex8 | 3 | id_my_base_lien_base_champ | A | 47712 | NULL | NULL | | BTREE | | |
| my_base_fiche_valeur | 1 | NewIndex8 | 4 | id_my_base | A | 47712 | NULL | NULL | | BTREE | | |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Best Answer
One change that could improve things is to convert the tables to InnoDB tables instead of being MyISAM. InnoDB is newer and works better in many ways than MyISAM.
You should enable the file per table option in
my.cnffor the database too when enabling InnoDB, and then allocate a good amount of memory for the InnoDB cache.However, I am quite sure that one can get better gains by rewriting the original query, and even better gains by restructuring the database.
One further benefit of resturcturing is that it would be easier to actually understand what is the purpose of the query. For example, I tried to read through the query and think what it does, but it is too complicated to understand when read through quickly.
I recommend that you communicate the restructuring need to the party who is writing the software.