Why is MySQL using this index? Can I force it to use another?


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,consumers_by_agent_id_where_tid0,consumers_by_brand_where_tid0,consumers_by_full_name_where_tid0,index_consumers_on_brand_id_and_agent_id
          key: consumers_by_full_name_where_tid0
      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)?