Optimal key for MySQL / InnoDB table with multi-part WHERE

Question

Given a (simplified) table

CREATE TABLE  `transactions` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CREATION_DT` datetime DEFAULT NULL,
  `STATUS_IND` int(11) DEFAULT NULL,
  `COMPANY_ID` bigint(20) DEFAULT NULL,
  `AMOUNT` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FKE7E81F1ED170D4C9` (`COMPANY_ID`),
  KEY `RPTIDX_CREATION_DT` (`CREATION_DT`),
) ENGINE=InnoDB AUTO_INCREMENT=5241784 DEFAULT CHARSET=latin1;

and a common, relatively expensive query

UPDATE transactions
SET STATUS_IND = 5
WHERE
COMPANY_ID = ?
 and (STATUS_IND = 3 or STATUS_IND = 7)
 and CREATION_DT >= ? and CREATION_DT <= ?

I'm trying to determine if there is a better index strategy. Reads will most commonly

SELECT * FROM transactions WHERE COMPANY_ID=? ORDER BY CREATION_DT

COMPANY_ID is fairly selective (we have hundreds of companies in the table, growing quickly). STATUS_IND is not very selective (there are 5 common statuses and a few less common ones), and it's value frequently changes (COMPANY_ID and CREATION_DT never change for a given row). CREATION_DT is fairly selective and getting more selective over time as we add more transactions to the system.

My initial thought is to replace the two existing keys with a compound key containing either COMPANY_ID+CREATION_DT or COMPANY_ID+STATUS_IND+CREATION_DT. Alternatively, possibly COMPANY_ID+CREATION_DT+STATUS_IND and change the order in the Update's WHERE clause?

Also, is there a good reference that explains how InnoDB uses secondary compound indexes?

Answer 1

My initial thought is to replace the two existing keys with a compound key...

This a better approach than individual keys.
Compound/covering index/key executes from left to right, meaning that the order of the list is important as it is read left to right. For example, if you define the index as: COMPANY_ID, CREATION_DT, STATUS_IND, the following combinations can trigger the use of the index:

1. COMPANY_ID
2. COMPANY_ID, CREATION_DT
3. COMPANY_ID, CREATION_DT, STATUS_IND

So if you only had CREATION_DT, or CREATION_DT, STATUS_IND - the index would not be used.

The order doesn't matter in the WHERE clause - just as long as the columns are referenced.

Keep in mind too that all indexes for a MyISAM table can only sum up to 1000 bytes long (767 bytes for InnoDB tables), as designed.

---

BTW: I rewrote your UPDATE statement:

UPDATE transactions
   SET STATUS_IND = 5
 WHERE COMPANY_ID = ?
   AND STATUS_IND IN (3, 7)
   AND CREATION_DT BETWEEN ? AND ?