Sql-server – Performance issue in Sql Server

indexperformancesql serversql-server-2008

I have a master product table as like below :

CREATE TABLE dbo.[products](
    [id] INT NOT NULL IDENTITY(1, 1),
    [product_code] VARCHAR(100) NOT NULL UNIQUE,
    [price] FLOAT NOT NULL,
    [brand] VARCHAR(100) NOT NULL,
    [colour] VARCHAR(100) NOT NULL
);

So, if I create this table, a clustered index will be created on id column and a non-clustered index on product_code column.

And I am using this table in a website to show the products. And I will be using the sql query like below.

Query 1

SELECT * FROM dbo.[products]
WHERE [brand] IN ('brand1', 'brand2', 'brand3', 'brand4', 'brand5')
AND [colour] IN ('colour1', 'colour1', 'colour1')
ORDER BY [product_code];

And there is an another option to search for bulk of product_codes like below.

Query 2

SELECT * FROM dbo.[products]
WHERE [product_code] IN ('product_code1', 'product_code2', 'product_code3');

The conditions can be more in Query 1 and product codes can be more in Query 2.

Did I created the index properly?

Or is there any better way to improve the performance?

Best Answer

Short answer: No, you need more indexes.

If you're going to create a significant number of queries involving [brand] and [colour], you should index both columns:

CREATE INDEX products_colour_idx ON dbo.products (colour);  
CREATE INDEX products_brand_idx ON dbo.products (brand);

If you mostly query using both columns, and there are not queries involving colour that do not also involve a brand, a multi-column index would be better, because by using just one index, the database can retrieve all the relevant rows. You would create it this way:

CREATE INDEX products_brand_colours_idx ON dbo.products (brand, colour);

The order of the columns [(brand, colour) vs. (colour, brand)] should be normally chosen in such a way that the most selective one comes first (basically: the one with more different values, first).

If you happen to have a significant number of queries with brand, colour and both brand and colour, you should have at least the two first indexes; if you need the fastest possible speed, have one index for (brand, colour) and another one for (colour). You don't need an index on purpose for (brand), the (brand, colour) one is good for looking for brands.

[product_code] will be indexed by the database automatically to enforce the UNIQUE constraint on it. See Create Unique Constraints and Unique Constraints and Unique Indexes. You don't need to create an index explicitly even if query_2 is frequent.

Related Question