0

I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index.

EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable`
WHERE (
    `mytable`.`brand_id` IN (21264)
    OR
    `mytable`.`agent_id` IN (4, 5, 6, 7, 9, 11)
    )
    AND (full_name IS NOT NULL AND full_name != '')
    AND (full_name LIKE '%kath%' OR id = 12 OR id = 0)   
    ORDER BY full_name ASC LIMIT 15

(That query was auto-generated, hence the strange parentheses.)

The table has a few inidices:

+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable |          0 | PRIMARY                                |            1 | id          | A         |     1329001 |     NULL | NULL   |      | BTREE      |         |               |
| mytable |          1 | mytable_by_agent_id                    |            1 | agent_id    | A         |        9561 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_brand                       |            1 | brand_id    | A         |        4598 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_full_name                   |            1 | full_name   | A         |     1329001 |     NULL | NULL   | YES  | BTREE          |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            1 | brand_id    | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            2 | agent_id    | A         |        1205 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

And I 'd expect the query to make use of index_mytable_on_brand_id_and_agent_id, but instead, EXPLAIN reveals that it's using mytable_by_full_name:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
         type: range
possible_keys: PRIMARY,mytable_by_agent_id,mytable_by_brand,mytable_by_full_name,index_mytable_on_brand_id_and_agent_id
          key: mytable_by_full_name
      key_len: 258
          ref: NULL
         rows: 811112
        Extra: Using where

How can I get this to use the other index? Why did it choose an index which won't help (because of the % at the start of the LIKE)?

2
  • 1
    The index you want it to use won't help either, because of OR mytable.agent_id IN (4, 5, 6, 7, 9, 11). There probably isn't a single index that will be useful for the assortment of criteria you throw at it. – mustaccio Sep 10 '20 at 17:56
  • @Akina thanks for that correction. I've updated indices' names above. – JellicleCat Sep 10 '20 at 20:27
1

No index will help with the WHERE clause -- part has OR, part has LIKE with an initial wild card.

So, the only hope for optimizing is to work on the ORDER BY. However, that is risky -- it might finish after looking at 15 rows, or it might look at the entire table without finding 15 rows. (Or some amount in between).

A single OR can usually be turned into UNION to good advantage. But 2 ORs is too messy to contemplate.

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.