This is useful when doing a text search on a SharePoint list that is bigger than the row limit, but is not big enough to warrent a migration to SQL server.
Updating status and adding @LanceDelano
Please add "in" to be delegateable to CDS as well!
I'd argue that from an architectural standpoint, the limits in a Sharepoint list are there because the list was never meant to be used as a large, long-term data store. This is the same consideration as a person had to make in the Microsoft Access era of self-service application development. All development which exceeds the intended use of a limited data store warrants at least considering a move to a fully capable database.