Order in which indicies are created affect which index the query optimizer will choose?

Question

If I create the following two indicies

ALTER TABLE requests ADD INDEX daily_ips(exec_date, ip_address);
ALTER TABLE requests ADD INDEX is_cached(exec_date, cached);

The output of show index from requests is the following

Table       Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
requests    1           daily_ips   1               exec_date   A           413         NULL    NULL    YES BTREE       
requests    1           daily_ips   2               ip_address  A           218334      NULL    NULL    YES BTREE       
requests    1           is_cached   1               exec_date   A           165         NULL    NULL    YES BTREE       
requests    1           is_cached   2               cached      A           165         NULL    NULL    YES BTREE       

I have the following query

EXPLAIN SELECT exec_date,
    100 * SUM(CASE WHEN cached = 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_no,
    100 * SUM(CASE WHEN cached != 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_yes
FROM requests
GROUP BY exec_date;

id  select_type table   type    possible_keys   key         key_len ref rows    Extra
1   SIMPLE  requests    index   NULL            daily_ips   263 NULL    436695  

However I would like to force the query optimizer to use the is_cached index instead of daily_ips index.

If I remove the daily_ips index and add it again

ALTER TABLE requests DROP INDEX daily_ips;
ALTER TABLE requests ADD INDEX daily_ips(exec_date, ip_address);

Then run the same EXPLAIN statement, the query optimizer chooses the is_cached index.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  requests    index   NULL    is_cached   6   NULL    440493  Using index

Is it expected behavior for the query optimizer to choose an index based on the order it was added?

How do I tell the query optimizer which index to use?

Answer 1

You can specify that which index sholud be used by query to run.

Try this:

EXPLAIN SELECT exec_date,
    100 * SUM(CASE WHEN cached = 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_no,
    100 * SUM(CASE WHEN cached != 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_yes
FROM requests USE INDEX(is_cached)
GROUP BY exec_date;

Differnce between USE INDEX and FORCE INDEX:

By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

UPDATE

Try this Query:

SELECT exec_date, 
       100 * SUM(IF(cached = 0, 1, 0)) / SUM(1) cached_no, 
       100 * SUM(IF(cached = 1, 1, 0)) / SUM(1) cached_yes 
FROM (SELECT exec_date, IF(cached = 'no', 0, 1) cached 
      FROM requests GROUP BY exec_date) AS A