Does Adding Indexes speed up String Wildcard % searches?-CodePudding

We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance?

CREATE TABLE [dbo].[Product](
    [ProductId] [int] NOT NULL,
    [ProductName] [varchar](250) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL, 
    ...
    CONSTRAINT [PK_ProductId] PRIMARY KEY CLUSTERED 
    (
       [ProductId] ASC
    )
 )

Proposed Index:

CREATE INDEX [IX_Product_ProductName] ON [dbo].[Product] ([ProductName])

for this query

select * from dbo.Product where ProductName like '%furniture%'

Currently using Microsoft SQL Server 2019.

CodePudding user response：

For a double ended wildcard search as shown, an index cannot help you by restricting the rows SQL Server has to look at - a full table scan will be carried out. But it can help with the amount of data that has to be retrieved from disk.

Because in ProductName like '%furniture%', ProductName could start or end with any string, so no index can reduce the rows that have to be inspected.

However if a row in your Product table is 1,000 characters and you have 10,000 rows, you have to load that much data. But if you have an index on ProductName, and ProductName is only 50 characters, then you only have to load 10,000 * 50 rather than 10,000 * 1000.

Note: If the query was a single ended wildcard search with % at end of 'furniture%', then the proposed index would certainly help.

CodePudding user response：

Creating a normal index will not help, but a full-text index will, though you would have to change your query to something like this:

select * from dbo.Product where ProductName CONTAINS 'furniture'

CodePudding user response：

Yes, the part before the first % is matched against the index. Of course however, if your pattern starts with %, then a full scan will be performed instead.

CodePudding user response：

It depends on the optimizer. Like usually requires a full table scan. if the optimizer can scan an index for matches than it will do an index scan which is faster than a full table scan.

if the optimizer does not select an index scan you can force it to use an index. You must measure performance times to determine if using an index scan decreases search time

Use with (index(index_name)) to force an index scan e.g.

select * from t1 with (index(t1i1)) where v1 like '456%'

SQL Server Index - Any improvement for LIKE queries?

If you use %search% pattern, the optimizer will always perform a full table scan.

Another technique for speeding up searches is to use substrings and exact match searches.