Very slow count with 7 million lines

Question

I got over 7 million rows in the table and

SELECT COUNT(*) FROM MyTable where MyColumn like '%some string%'

gives me 20,000 lines and takes more than 13 seconds.

In MyColumn, the table has a CONTINUOUS INDEX.

Is there a way to improve speed?

Answer 1

Personal search patterns are can notoptimized using T-SQL and will not use an index

Take a look at SQL Server full-text search

Answer 2

You can try full-text search or a text search engine such as Lucene .

Answer 3

, , Unicode .

SELECT COUNT(*) 
FROM MyTable 
WHERE MyColumn COLLATE Latin1_General_BIN2 LIKE '%some string%'

, " " SQL Server MVP Deep Dives,

, , . MyColumn MyTable.id, . , , . , . .

.