Mysql 5.5 slow “Copying to tmp table” and strange profiling
0
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 | | |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Oct 4 '17 at 2:40
This question came from our site for system and network administrators.
add a comment |
0
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 | | |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Oct 4 '17 at 2:40
This question came from our site for system and network administrators.
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. UseGROUP BYinstead ofSELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...
– Tero Kilkanen
Sep 27 '17 at 20:20
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56
add a comment |
0
0
0
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 | | |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
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 | | |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
mysql
asked Sep 27 '17 at 15:33
r4dius
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Oct 4 '17 at 2:40
This question came from our site for system and network administrators.
migrated from serverfault.com Oct 4 '17 at 2:40
This question came from our site for system and network administrators.
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. UseGROUP BYinstead ofSELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...
– Tero Kilkanen
Sep 27 '17 at 20:20
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56
add a comment |
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. UseGROUP BYinstead ofSELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...
– Tero Kilkanen
Sep 27 '17 at 20:20
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. Use
GROUP BY instead of SELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...– Tero Kilkanen
Sep 27 '17 at 20:20
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. Use
GROUP BY instead of SELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...– Tero Kilkanen
Sep 27 '17 at 20:20
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56
add a comment |
2 Answers
2
active
oldest
votes
0
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.cnf for 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.
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
add a comment |
0
Line 5 of the query
AND val_varchar_1!=''
could become
AND LEN(val_varchar_1)>0
near line 25 there is this code
( val_int_1 LIKE '%valon%'))
that can not possibly be true because val_int_1 is defined as INT
There are three references to
id_my_base_lien_base_champ
with three different values - possibly net of mutually exclusive.
One redundant index is my_base_fiche_valeur NewIndex1 that could be removed with no harm.
Please post your .cnf for additional analysis.
You will be required to work with your software vendor and please use INNODB rather than MyISAM that will be GONE in v8.
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f187586%2fmysql-5-5-slow-copying-to-tmp-table-and-strange-profiling%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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.cnf for 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.
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
add a comment |
0
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.cnf for 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.
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
add a comment |
0
0
0
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.cnf for 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.
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
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.cnf for 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.
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
answered Sep 30 '17 at 1:24
Tero KilkanenTero Kilkanen
1115
1115
add a comment |
add a comment |
0
Line 5 of the query
AND val_varchar_1!=''
could become
AND LEN(val_varchar_1)>0
near line 25 there is this code
( val_int_1 LIKE '%valon%'))
that can not possibly be true because val_int_1 is defined as INT
There are three references to
id_my_base_lien_base_champ
with three different values - possibly net of mutually exclusive.
One redundant index is my_base_fiche_valeur NewIndex1 that could be removed with no harm.
Please post your .cnf for additional analysis.
You will be required to work with your software vendor and please use INNODB rather than MyISAM that will be GONE in v8.
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
add a comment |
0
Line 5 of the query
AND val_varchar_1!=''
could become
AND LEN(val_varchar_1)>0
near line 25 there is this code
( val_int_1 LIKE '%valon%'))
that can not possibly be true because val_int_1 is defined as INT
There are three references to
id_my_base_lien_base_champ
with three different values - possibly net of mutually exclusive.
One redundant index is my_base_fiche_valeur NewIndex1 that could be removed with no harm.
Please post your .cnf for additional analysis.
You will be required to work with your software vendor and please use INNODB rather than MyISAM that will be GONE in v8.
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
add a comment |
0
0
0
Line 5 of the query
AND val_varchar_1!=''
could become
AND LEN(val_varchar_1)>0
near line 25 there is this code
( val_int_1 LIKE '%valon%'))
that can not possibly be true because val_int_1 is defined as INT
There are three references to
id_my_base_lien_base_champ
with three different values - possibly net of mutually exclusive.
One redundant index is my_base_fiche_valeur NewIndex1 that could be removed with no harm.
Please post your .cnf for additional analysis.
You will be required to work with your software vendor and please use INNODB rather than MyISAM that will be GONE in v8.
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
Line 5 of the query
AND val_varchar_1!=''
could become
AND LEN(val_varchar_1)>0
near line 25 there is this code
( val_int_1 LIKE '%valon%'))
that can not possibly be true because val_int_1 is defined as INT
There are three references to
id_my_base_lien_base_champ
with three different values - possibly net of mutually exclusive.
One redundant index is my_base_fiche_valeur NewIndex1 that could be removed with no harm.
Please post your .cnf for additional analysis.
You will be required to work with your software vendor and please use INNODB rather than MyISAM that will be GONE in v8.
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
answered Sep 30 '17 at 14:12
Wilson HauckWilson Hauck
75349
75349
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f187586%2fmysql-5-5-slow-copying-to-tmp-table-and-strange-profiling%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Could you post 4 items for us? SHOW CREATE TABLE my_base_fiche; SHOW CREATE TABLE my_base_fiche_valeur; SHOW INDEX FROM my_base_fiche; SHOW INDEX FROM my_base_fiche_valeur; we will have a better clue on table size/cardinality, index count.
– Wilson Hauck
Sep 27 '17 at 18:25
From my quick inspection during formatting the query into more readable form, I'd say that you could write the query much more efficiently. Use
GROUP BYinstead ofSELECT DISTINCT. If possible, reformat the database structure to better match your needs. I am not surprised that such a complicated query would give odd results in the query analyser...– Tero Kilkanen
Sep 27 '17 at 20:20
@WilsonHauck no problem I added it to the question :)
– r4dius
Sep 29 '17 at 12:52
@TeroKilkanen as for the queries and the database structure I can't edit anything from myself except managing some indexex maybe, I'm only administrating the server not the actual code :/
– r4dius
Sep 29 '17 at 12:56