I face a weird situation where the same query is executing fast in the environment even though the index is unavailable and it's slow in another environment where the index is available. Can someone please help me with this? The table structure is identical in both the environments and also the data is around 2 million.
I did a select to find the execution time on both the servers. The query is executed in 2 sec in the environment where there is no index available and it took around 80 sec in the environment where an index is available.
UPDATE jobs_feed
set posted_to_gfj=3
WHERE posted_to_gfj = 1
AND posted_date < '2022-06-28'
ORDER BY posted_date ASC limit 8000;
column:posted_to_gfj tinyint(1)
column:posted_date datetime
CodePudding user response：
The order of the columns in an index matters. This is better than the former answer:
INDEX(posted_to_gfj, posted_date)
In general, list column(s) tested with = first. Meanwhile, the order of WHERE clauses is not relevant.
With this order, even the LIMIT can be handled in the `INDEX.
If you currently have INDEX(posted_to_gfj), DROP it; it is in the way.
A possible explanation for the differences between the servers... EXPLAIN can be different depending on the distribution of the data. In particular, (and with INDEX(posted_date)) if most of the rows are in the requested range, that index won't be used.
CodePudding user response：
Are the columns in the index in the same order they're used in the query? as in
posted_to_gfj, posted_date
vs.
posted_date, posted_to_gfj?