This question is related to my old question. The below query was taking 10 to 15 seconds to execute:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0)
In some articles I saw that using CAST
and CHARINDEX
will not benefit from indexing. There are also some articles that say using LIKE '%abc%'
will not benefit from indexing while LIKE 'abc%'
will:
http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568
In my case I can rewrite the query as:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no] LIKE '%123456789%'
This query gives the same output as the previous one. I have created a nonclustered index for column Phone no
. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.
How does LIKE '%123456789%'
benefit from indexing?
Why do the listed articles state that it will not improve performance?
I tried rewriting the query to use CHARINDEX
, but performance is still slow. Why does CHARINDEX
not benefit from the indexing as it appears the LIKE
query does?
Query using CHARINDEX
:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [Company].dbo.[customer]
WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 )
Execution plan:
Query using LIKE
:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [Company].dbo.[customer]
WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'
Execution plan: