Indexing not working when large data affected in where condition

user_5518513 0
Viewed 261

I have a query. As follows

SELECT  SUM(principalBalance) as pos, COUNT(id) as TotalCases,
        SUM(amountPaid) as paid, COUNT(amountPaid) as paidCount,
        SUM(amountPdc) as Pdc, SUM(amountPtp), COUNT(amountPtp)
    FROM  caseDetails USE INDEX (updatedAt_caseDetails)
    WHERE  updatedAt BETWEEN '2016/06/01 00:00:00' AND '2016/06/30 23:59:00'

It uses indexing effectively. Screen shot of result of explain:Result of explain There are 154500 records in date range '2016/06/01 00:00:00' AND '2016/07/26 23:59:00'.

But when I increase data range as,

SELECT SUM(principalBalance) as pos, COUNT(id) as TotalCases, SUM(amountPaid) as paid, COUNT(amountPaid) as paidCount, SUM(amountPdc) as Pdc, SUM(amountPtp), COUNT(amountPtp) FROM caseDetails USE INDEX (updatedAt_caseDetails) WHERE updatedAt BETWEEN '2016/06/01 00:00:00' AND '2016/07/30 23:59:00'

Now this is not using indexing. Screen shot of result of explain:Result of explain There are 3089464 records in date range '2016/06/01 00:00:00' AND '2016/07/30 23:59:00'

After increasing date range query not using indexing anymore, so it gets too much slow. Even after I am forcing to use index. I am not able to figure out why this is happening as there is no change in query as well as indexing. Can you please help me to know about why this is happening.

indexing mysql
3 Answers

Hey it has been long time I had ask this question, Now I have better solution for this which is working really smoothly for me. I hope my answer may help someone.

I used Partitioning method, and observed that performance of the query is really high now. I alter table by creating range partitioning on updatedAt column.

Range Partitioning

user_55185130
Related