Please tell me how to hit the index after where and orderBy in Mysql?

I probably have a requirement now, which is to query a list in the database. The data table probably looks like this:

And my current SQL statement is probably like this

<code class="sql">SELECT * FROM `TABLE` WHERE B = 1 AND C = 1 AND D = 2016-08-05 ORDER BY E DESC</code>

In this case, how should I build an index so that it can hit when querying? Is it possible to create a composite index of index(['B', 'C', 'D', 'E'])?

Reply content:

I probably have a requirement now, which is to query a list in the database. The data table probably looks like this:

And my current SQL statement is probably like this

<code class="sql">SELECT * FROM `TABLE` WHERE B = 1 AND C = 1 AND D = 2016-08-05 ORDER BY E DESC</code>

In this case, how should I build an index so that it can hit when querying? Is it possible to create a composite index of index(['B', 'C', 'D', 'E'])?

Theoretically, it can be built like this, because the left prefix principle of the index will hit the three fields B, C, and D in sequence. However, unfortunately, if the E field is only used for sorting, the index cannot be triggered.
Because when the order by field appears in the where condition, the index will be used without the need for sorting operations. In other cases, order by will not perform sorting operations.
For details, you can read this article to analyze the relationship between orderby and index
http://www.cnblogs.com/zhaoyl...

mysql force specified index FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) ...
The above SQL statement only uses the index built on FIELD1, not the indexes on other fields.