How to search millions of record in SQL table faster?

sql query-optimization sql-like
user737063 picture user737063 · May 4, 2011 · Viewed 34.7k times · Source

I have SQL table with millions of domain name. But now when I search for let's say

SELECT * 
  FROM tblDomainResults 
 WHERE domainName LIKE '%lifeis%'

It takes more than 10 minutes to get the results. I tried indexing but that didn't help.

What is the best way to store this millions of record and easily access these information in short period of time?

There are about 50 million records and 5 column so far.

Answer

Igor Nazarenko picture Igor Nazarenko · May 4, 2011

Most likely, you tried a traditional index which cannot be used to optimize LIKE queries unless the pattern begins with a fixed string (e.g. 'lifeis%').

What you need for your query is a full-text index. Most DBMS support it these days.

Related questions

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two …

Read More
SQL: How to properly check if a record exists

While reading some SQL Tuning-related documentation, I found this: SELECT COUNT(*) : Counts the number of rows. Often is improperly used to verify the existence of a record. Is SELECT COUNT(*) really that bad? What's the proper way to verify the …

Read More
How to find out what is locking my tables?

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table. I've experimented a bit with sys.dm_tran_locks …

Read More