Mysql – How to avoid index_merge in a query

indexmysql-5.5optimization

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.

Best Answer

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