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

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)

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); 

You have to add index for colA and colB. That will resolve the issue.

This is your query:

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

One approach to indexing would be table_name(colA, colB, date). This is a covering index for the query. However, it will not eliminate the sorting.

You could try this approach:

SELECT dateTo
FROM (SELECT `date` as dateTo, colB
      FROM table_name tbl
      WHERE `colA` = 223 
      ORDER BY `date` DESC
     ) t
WHERE `colB` <> 1;

The subquery should be able to make use of a query on table_name(colA, date). It will need to materialize the entire result set, but then choosing colB <> 1 should be pretty fast. I'm not thrilled with this approach, because it assumes that the subquery remains ordered when read by the outer query -- but that is true in MySQL.

How many different value in colB? If it can have only 0 and 1, then change the filter to

colB = 0

and add this

INDEX(colA, colB, date)

(The date must be last, but A and B can be in either order.) Only then can the all the filtering and the ORDER BY be handled by the INDEX.

If colB has more than 2 values, then let's slightly improve on Gordon's solution:

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

with INDEX(colA, colB, date), with the columns in exactly that order. This index would be a "covering" index.