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 | | |
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
The update query can be forced to use index using below method: