Is there any DB index that can improve the performance of 'like %keyword%' queries?

Question

I know indexing on a text field can help with searches with like 'keyword%'.

But is there any cross-database indexing that can improve full text search with like '%keyword%'?

And generally, for a text fields with less than 1000 chars, when does the performance of like %keyword% query becomes unacceptable for a web request? Say when the table reaches 10K rows?

Answer 1

You can use FULLTEXT INDEX and seach using specific commands - on MSSQL could use CONTAINS(column, '* keyword *') or FREETEXT - https://msdn.microsoft.com/en-us/library/ms142583.aspx

Regarding your question about performance, it's strongly linked to your infrastructure, your data or your search terms.