How to force mysql UPDATE query to use index? How to enable mysql engine to automatically use the index instead of forcing it?

Question

Below is the update query/query plan that is not using the compound index that was created recently. The explain shows that its not using the compound index named radacctupdate which i think will make the update query faster. There are other indexes on table too which are used by other queries.

EXPLAIN UPDATE radacct SET acctstoptime = '2017-01-08 11:52:24',
acctsessiontime = unix_timestamp('2017-01-08 11:52:24') - unix_timestamp(acctstarttime),
acctterminatecause = '', acctstopdelay = 14855646 
WHERE acctstoptime IS NULL AND
nasipaddress = '102.34.56.234' AND acctstarttime <= '2017-01-08 11:52:24';

************* 1. row ***********
           id: 1
  select_type: SIMPLE
        table: radacct
         type: range
possible_keys: acctstarttime,acctstoptime,nasipaddress,radacctupdate
          key: nasipaddress
      key_len: 17
          ref: const
         rows: 94
        Extra: Using where; Using MRR

Below is show index from <table> output

+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radacct |          0 | PRIMARY         |            1 | radacctid       | A         |    29299212 |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | username        |            1 | username        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | framedipaddress |            1 | framedipaddress | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | acctsessionid   |            1 | acctsessionid   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | acctsessiontime |            1 | acctsessiontime | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| radacct |          1 | acctstarttime   |            1 | acctstarttime   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| radacct |          1 | acctstoptime    |            1 | acctstoptime    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| radacct |          1 | nasipaddress    |            1 | nasipaddress    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | acctuniqueid    |            1 | acctuniqueid    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | radacctupdate   |            1 | acctstoptime    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| radacct |          1 | radacctupdate   |            2 | nasipaddress    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| radacct |          1 | radacctupdate   |            3 | acctstarttime   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Cain

Answer 1

The update query can be forced to use index using below method:

UPDATE radacct use index(indexname_indx100) 
SET acctstoptime=  '2017-01-08 14:58:27',
acctsessiontime =  unix_timestamp('2017-01-08 14:58:27')-                                   unix_timestamp(acctstarttime),acctterminatecause = '',              acctstopdelay=1483866808
WHERE acctstoptime IS NULL AND nasipaddress='22.194.36.2' AND
acctstarttime <= '2017-01-08 14:58:27';

Cain

MySQL ignores index hints in UPDATE statements – Javelin

@Javelin - That may be fixed in MySQL 8.0, which uses common code for SELECT and UPDATE. – Terce

Which version of MySQL ignores the index hints? v5.6, 5.7 & 8.0 docs specifically claim they apply to both SELECT and UPDATE: dev.mysql.com/doc/refman/5.6/en/index-hints.html – Gowen

Also, specifically on v5.7, explain update ... use index (... shows using the index as instructed. – Gowen