Luke101 Luke101 - 2 years ago 67
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 Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download