I have the following query in a production database server(master):
SELECT *
FROM Enlace_llamadas
where id_empresa IN (1,5,33,36,40,44,48,53,55,111,115,125,127,151,167,175,181,185,187,189,195,199,201,203,207,209,221,223,231,233,239,241,243,244,247,249,251,253,257,259,261,276,277,281,283,287,291,293,301,305,307,313,315,319,323,339,341,343,345,359,367,371,379,381,383,385,387,391,393,395,397,421,423,425,429,431,433,437,439,441,445,447,451,454,463,469,477,479,481,489,491,495,497,501,507,510,514,520,522)
AND date(fecha_inicio)=date(NOW())
and Enlace_llamadas.fecha_fin='0000-00-00 00:00:00'
and TIMESTAMPDIFF(MINUTE,fecha_inicio, NOW()) >= '5';
Running EXPLAIN inside this server, this was the result:
+----+-------------+-----------------+------+------------------------------------------------------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+------------------------------------------------------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | Enlace_llamadas | ALL | id_empresa_id_agente,idx_empresa_campana,id_empresa_cliente,id_empresa | NULL | NULL | NULL | 41330453 | Using where |
+----+-------------+-----------------+------+------------------------
this query takes long time , almost 40 seconds of execution and the machine resources use a lot of CPU above 0f 500%. Executing SHOW INDEX FROM nombre_tabla, this was the result table.
| Enlace_llamadas | 0 | PRIMARY | 1 | id | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_campana | 1 | id_campana | A | 19700 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_cliente | 1 | id_cliente | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_numero_cliente | 1 | id_numero_cliente | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_id_agente | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_id_agente | 2 | id_agente | A | 19468 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | idx_empresa_campana | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | idx_empresa_campana | 2 | id_campana | A | 19569 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_cliente | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_cliente | 2 | id_cliente | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | i_Enlace_llamadas_fechas | 1 | fecha_inicio | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | i_Enlace_llamadas_fechas | 2 | fecha_fin | A | 41330863 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_agente | 1 | id_agente | A | 8516 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
+-----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)
This same query in another server(slave) takes 0.21 seconds. This is the execution plan in the slave server.
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+------+-------------------------------------------------------------------------------------------------+--------------------------+---------+-------+------+------------------------------------+
| 1 | SIMPLE | Enlace_llamadas | ref | id_empresa,i_Enlace_llamadas_fechas,id_empresa_id_agente,id_empresa_cliente,idx_empresa_campana | i_Enlace_llamadas_fechas | 8 | const | 276 | Using index condition; Using where
The indexes on this table from this server
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Enlace_llamadas | 0 | PRIMARY | 1 | id | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_campana | 1 | id_campana | A | 78826 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_cliente | 1 | id_cliente | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_numero_cliente | 1 | id_numero_cliente | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_agente | 1 | id_agente | A | 9389 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | i_Enlace_llamadas_fechas | 1 | fecha_fin | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | i_Enlace_llamadas_fechas | 2 | fecha_inicio | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_id_agente | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_id_agente | 2 | id_agente | A | 42663 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_cliente | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | id_empresa_cliente | 2 | id_cliente | A | 46034401 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | idx_empresa_campana | 1 | id_empresa | A | 18 | NULL | NULL | | BTREE | | |
| Enlace_llamadas | 1 | idx_empresa_campana | 2 | id_campana | A | 22271 | NULL | NULL | | BTREE | | |
+-----------------+------------+--------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)
What is the reason from this?
Best Answer
Once you format your output correctly, you can see that the definition of
i_Enlace_llamadas_fechasdiffers between the two servers -- the column order is reversed in the second server, which makes it suitable for the query there.