Fastest way to find string by substring in SQL?

sql sql-server full-text-search query-optimization sql-server-2008-r2
msergey picture msergey · Jul 11, 2011 · Viewed 35.6k times · Source

I have huge table with 2 columns: Id and Title. Id is bigint and I'm free to choose type of Title column: varchar, char, text, whatever. Column Title contains random text strings like "abcdefg", "q", "allyourbasebelongtous" with maximum of 255 chars.

My task is to get strings by given substring. Substrings also have random length and can be start, middle or end of strings. The most obvious way to perform it:

SELECT * FROM t LIKE '%abc%'

I don't care about INSERT, I need only to do fast selects. What can I do to perform search as fast as possible?

I use MS SQL Server 2008 R2, full text search will be useless, as far as I see.

Answer

Randy picture Randy · Jul 11, 2011

if you dont care about storage, then you can create another table with partial Title entries, beginning with each substring (up to 255 entries per normal title ).

in this way, you can index these substrings, and match only to the beginning of the string, should greatly improve performance.

Related questions

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

I am getting following error in my SQL server 2008 R2 database: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tblArmy' because it is not full-text indexed.

Read More
When should you use full-text indexing?

We have a whole bunch of queries that "search" for clients, customers, etc. You can search by first name, email, etc. We're using LIKE statements in the following manner: SELECT * FROM customer WHERE fname LIKE '%someName%' Does full-text …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More