Lets consider I have a table A with primary key(id) and an index(created_date). When I run a query with limit its running in milliseconds.
Example query:
select id from A where created_date > '2022-09-21 00:00:00' limit 10;
But the same query is taking 5 seconds when there is no records or available records less than the limit.
Example Scenarios:
- If we have 15(greater than 10(limit)) records for the above condition its taking ~13 milliseconds.
- If we have 9 records for the above condition its taking ~5 seconds.
- If we have 0 records for the above condition its taking ~5 seconds.
Any thoughts and suggestions to improve the performance?
I am using MySql 8.