how to optimise query for 10+ millions records

[Azhar Rahi]

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?