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      |         |               |
+-----------------------+------------+-----------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

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. 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
  • @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
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.