Adding index for faster search-CodePudding

I cannot remember how to add an index for faster lookup from the table. I have a primary key, but I want to have an index for faster lookup of rows by the Component code. For a faster reading of:

select * from prices where ComponentCode like '%something%'

Look at the two last lines of the script

What am I doing wrong?

CREATE TABLE [dbo].[Prices] (
    Id int IDENTITY(1,1) NOT NULL,
    ComponentCode   varchar(255),
    Description VARCHAR(255),
    PriceUnit   float,
    Price   float
);
GO
ALTER TABLE [dbo].[Prices]
ADD CONSTRAINT [PK_Prices]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO
CREATE CLUSTERED INDEX CI_Prices ON [dbo].[Prices] (ComponentCode);
GO

CodePudding user response：

You can think of an index as a sorted tree that holds the values of the column and pointers back to the rows they come from. This improves the speed of queries since it's faster to search a sorted tree than an unsorted list.

However, in this query, you're looking for a substring in the middle of the value (since you have a % wildcard at the beginning of the right operand). In this case, the fact that the values of ComponentCode are stored in a sorted index won't help you, and the database just ignores the index.

CodePudding user response：

Like this:

CREATE INDEX IX_Prices_ComponentCode ON [dbo].[Prices] (ComponentCode);

But as others have said, it's not a silver bullet for wildcard searches.