Question :
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)?
Answer :
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.