how to optimise query for 10+ millions records
Posted by:
Azhar Rahi
Date: February 09, 2012 03:30AM
Hello How all are here.. I have recently written a query which runs perfect under the small records but gets slow when the records exceeds upto millions. The records in the table can be increased upto millions within 3,4 days. I have also applied indexes on the columns and used them in where clause, but in some cases it still scans the whole table.
So the query has a left join with another table, so that if the records does not match in the outer table, then it returns the null records. The problem is that the outer table contains a column 'DestinationName', on which we have made a filter. Well its quite possible that user want to see all destinations.So what I have have done to fulfil this, is to apply a condition with ifnull statement in where clause:
i.e.
IFNULL(destination.DestinationName,'Unknown') LIKE @l_Destination
Now when user asks for records against all destinations, it scans the whole table and hence ignore the indexes. I got this by the query anaylyzing using Expain keyword. But if I pass any keyword in @l_Destination, then the Explain shows that it has hit the indexes. So we may say that for all destinations, there can be null destinations as well, in which case the indexes are skipped.
Can anybody helps me regarding this whole scanning? So that we can avoid this.
Also we have to show only 100 records on the Page, but we are doing paging in the code, not in the database. Can it help us also to stop full scanning of the table?
Subject
Views
Written By
Posted
how to optimise query for 10+ millions records
2875
February 09, 2012 03:30AM
1708
February 09, 2012 05:31AM
1369
February 09, 2012 05:38AM
1272
February 09, 2012 05:58PM
1254
February 11, 2012 11:26PM
1504
February 12, 2012 10:12PM
1359
February 13, 2012 02:18PM
1573
February 14, 2012 02:33AM
1354
February 14, 2012 07:24AM
1377
February 15, 2012 07:30AM
1836
February 16, 2012 03:54AM
1273
February 17, 2012 01:20AM
1227
February 17, 2012 03:57PM
1509
February 17, 2012 05:12PM
1316
February 18, 2012 07:18AM
1374
February 21, 2012 03:25AM
1366
February 24, 2012 10:45AM
1309
February 20, 2012 02:21AM