Some things you can change, and some you can't, right? And for whatever reason, you may need to query a huge table, and not be able to index the field that you need to query. I ran into this situation recently.
Well, if it's a log table, that at least has some kind of index (preferably clustered), and you only need to query recent records, you're in luck. Here's a workaround that can be useful:
Select *
From DBA..CommandLog
Where
ID > (Select Max(ID) from DBA..CommandLog)- 5000
And Command like '%ApplicationErrorLogs%'
And DatabaseName like 'rds_qt_DB'
How does this work? This is a log table, and there's a clustered index on the ID field of this table. Many, many tables are structured like this.
You use the subquery to get the Max(ID) of the table. Then, subtract 5000 to only query the last 5000 records. That way, the index will be used to do the initial filter, then the other filters will be applied to that limited set. It should be much faster.
