0

I have multiple queries using index_merge but for put an example, I show you the following query

EXPLAIN select count(*) From Clientes  Where id_campana='1849' and id_empresa='55' and (id_estado_cliente='1' or id_estado_cliente='5' or id_estado_cliente='9'  or id_estado_cliente='10' or id_estado_cliente='13' or id_estado_cliente='7');

Running Explain Statement, this was the result

+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+
| id | select_type | table    | type        | possible_keys                                                                       | key                   | key_len | ref  | rows  | Extra                                               |
+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+
|  1 | SIMPLE      | Clientes | index_merge | id_empresa,id_campana,id_estado_cliente,id_empresa_campana,idx_mejora_perf_clientes | id_empresa,id_campana | 2,4     | NULL | 35564 | Using intersect(id_empresa,id_campana); Using where |
+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+

The only thing I have done to optimize query is create a compound query and use index_hint

this are the indexes which exist on this table

+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                 | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Clientes |          0 | PRIMARY                  |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          0 | id_id_empresa            |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          0 | id_id_empresa            |            2 | id_empresa          | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id                       |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa               |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_campana               |            1 | id_campana          | A         |       61752 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_estado_cliente        |            1 | id_estado_cliente   | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa_campana       |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa_campana       |            2 | id_campana          | A         |       27982 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            2 | id_campana          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            3 | id_estado_cliente   | A         |       18363 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            4 | id_resultado_agente | A         |      110214 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

but the problem if the developers will modify this statement to solve this condition but my doubt is exist something method to make it transparent.

0

Start by cleaning up the existing indexes:

id isn't useful as id the primary key already and should be dropped.

Both id_empresa and id_empresa_campana are superseded by idx_mejora_perf_clientes because idx_mejora_perf_clientes begins with the entire index columns of id_empresa and id_empresa_campana and these two indexes can be dropped.

With those removed hopefully your query will result in a range search on idx_mejora_perf_clientes. It could bad statistics or and old mysql version that doesn't see this as the preferred option.

Failing that you can remove index_merge_intersection from the optimizer_switch session variable for this query.

ref: mysql manual on index merge

  • I cannot drop indexes id_empresa and id_empresa_campana because of unknowledge of the code from the application, the only thing that I did was to create idx_mejora_perf_clientes, those indexes were created by the previous developers of the application and the previous year I was hired by the company as dba to improve this application but with minimal impact – Iori_Yagami Jan 7 at 23:14

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.