I always thought that it was effective for mysql to create an index in the order condition, and it was ineffective.

I always thought that it was effective for mysql to create an index in the order condition, but it didn't work. EXPLAIN SELECT *
FROM 'order2'
WHERE 1
Order by 'create _ time' DESC

Full table scan...

Reply to discussion (solution)

The index in order is not so used.

Your current condition is the full table. no matter what index you have, it will certainly be the whole table again.

I understand that order indexes should be used in two ways.
1. when there is a limit
2. in the select return value of the order field, this is complicated, that is, only the order field is returned; or several fields are returned. in this case, the index must be a joint index in the order of these fields.

The index in order is not so used.

Your current condition is the full table. no matter what index you have, it will certainly be the whole table again.

I understand that order indexes should be used in two ways.
1. when there is a limit
2. in the select return value of the order field, this is complicated, that is, only the order field is returned; or several fields are returned. in this case, the index must be a joint index in the order of these fields.

I just used the order index again. it seems that it is still valid. Is there any junk data that affects the index of the entire table? (Different databases and tables)

Add an index condition such as explain select *
FROM 'order2'
WHERE 1 and create_time> 0
Order by 'create _ time' DESC

If the data base in your table is too small or the data distribution is not large, the mysql Query optimizer will remove the index and perform a full table scan.
This kind of automatic optimization behavior is not predictable. when the data volume is small, sometimes indexes are not used.

If the data base in your table is too small or the data distribution is not large, the mysql Query optimizer will remove the index and perform a full table scan.
This kind of automatic optimization behavior is not predictable. when the data volume is small, sometimes indexes are not used.

Yes, I found the problem.
In addition, if the search condition detects too much data, the entire table will be scanned.