Luke101 Luke101 - 6 months ago 8
SQL Question

Which databases support efficient LIKE "%roger%"? (How can I make it faster?)

I am looking for a database that supports indexes like this:

select name from table where name Like "%roger%"


I have over a hundred thousand rows of text I need to search and SQL Server is taking too long.

Are there any databases or indices specifically designed for this query? (How can I make the above query faster?)

Answer

Normal (b-tree) based indexes on any database can't work by design for a LIKE query that starts with %, as that forces a full scan. Full-text indexes are the alternative for this type of query, or putting your searchable keywords into a different table and indexing that (may or may not be a viable solution, depending on your situation).

Comments