Adding index for faster search

Question

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

Do you really require a leading wildcard? (`'%something%'`) If so a B tree index will be of limited use (just gives you something a bit narrower to scan). For non prefix searches you need to be looking at full text indexing — Martin Smith, Jan 05 '23 at 15:35
If this is about MS SQL Server, which seems so, check this: https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries — Renat, Jan 05 '23 at 15:37
A table can only have one `CLUSTERED` index. That roughly defines how the table data is physically organized. Change `CLUSTERED` to `NONCLUSTERED` or drop the keyword entirely since non-clustered is the default. — T N, Jan 05 '23 at 15:39
I would suggest not using the float datatype for prices. You should instead a precise numeric type like numeric. — Sean Lange, Jan 05 '23 at 16:14

score 1 · Answer 1 · answered Jan 05 '23 at 15:36

1

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.

answered Jan 05 '23 at 15:36

Mureinik

297,002
52
306
350

3

The index will have ComponentCode and ID, so is narrower to scan, and may cover some queries. – David Browne - Microsoft Jan 05 '23 at 15:47
@DavidBrowne-Microsoft OP's query doesn't use `id`, so the index shouldn't cover it. What am I missing? – Mureinik Jan 05 '23 at 15:56
2

Even with a wildcard search, an index scan followed by a key lookup can be much faster that a table scan, because the amount of data read to scan the index is typically much smaller. (This is actually one of the cases where storing a large amount of "covering" columns is detrimental.) – T N Jan 05 '23 at 16:04
Nonclustered indexes always contain the "row locator", which here is the clustered index key ID. Non-unique non-clustered indexes are stored as unique indexes with the missing clustered index key columns added to the nonclustered index keys to make it unique. For unique nonclustered indexes the clustered index key columns are stored as "included columns" on the leaf pages. – David Browne - Microsoft Jan 05 '23 at 16:04
@DavidBrowne-Microsoft that I get. But the query OP is asking about is `select * from prices where ComponentCode like '%something%'`. The `id` isn't in the **query**, so having it implicitly in the index shouldn't help either. – Mureinik Jan 05 '23 at 16:10
The only way to know if an index speeds up a query is to make tests and measure... – Olivier Jacot-Descombes Jan 05 '23 at 16:16

score 1 · Answer 2 · answered Jan 05 '23 at 15:46

1

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.

answered Jan 05 '23 at 15:46

David Browne - Microsoft

80,331
6
39
67

Adding index for faster search

2 Answers2