创建INDEX以改善ORDER BY导致的慢执行时间

Question

I have the following query in a PHP function. This gets called a number of times depending on a number of factors, but even if it is executed only 1 time it takes a long time.

SELECT  `date` as dateTo
FROM    table_name tbl
WHERE    `colA` = 223 and `colB` <> 1
ORDER BY `date` DESC
LIMIT 1

The database table has about 2 million records and the ORDER BY is slowing the execution time.

What is the best INDEX I could have in this scenario?

Would an index on date only be beneficial or would I have to include colA and colB?

-----

I ended up using this query,

SELECT  `ColA`,`date`, `ColB`
FROM  atm_status_log
WHERE  `ColA` = 223
HAVING  `ColB` <> 1
ORDER BY `date` DESC
LIMIT 1;

and this INDEX, INDEX(colA, colB, date)

Answer 1

You have to add a index on date, colA, colB. Please keep in mind the order off that index matters.

ALTER TABLE table_name ADD KEY index_name (date, colA, colB);