The search you mentioned there specifically is problematic on any large data-set, because in its current(ly unoptimized) state it wont be able to use index-seeking to arrive at an answer.
This means that at best it will need to do a index-scan (in practice a full table-scan on the index consisting of a subset of the table-data). The results of a query like that can't be obtained fast, at least not milliseconds fast, and its not well suited for caching or reusability.
There are (obviously) ways around this, but they all involve indirection and workarounds.
Anyway, as a rule of thumb: Any time you want to do general full-text search queries on relational data, the LIKE operator will very quickly meet its limits and depending on how much outside its bounds you want to go, you will find yourself having to do quite a bit of work.
And in those cases you mihgt want to use the full-text capabilities of your RDBMS if it has any. Or maybe something like Elasticsearch might be a better fit.
This means that at best it will need to do a index-scan (in practice a full table-scan on the index consisting of a subset of the table-data). The results of a query like that can't be obtained fast, at least not milliseconds fast, and its not well suited for caching or reusability.
There are (obviously) ways around this, but they all involve indirection and workarounds.
Anyway, as a rule of thumb: Any time you want to do general full-text search queries on relational data, the LIKE operator will very quickly meet its limits and depending on how much outside its bounds you want to go, you will find yourself having to do quite a bit of work.
And in those cases you mihgt want to use the full-text capabilities of your RDBMS if it has any. Or maybe something like Elasticsearch might be a better fit.
Disclaimer: I've never used Elasticsearch myself.