-1

I have a db that supports a free-form addition of values to products. I'm using a key-value structure because individual products can have wildly different key/value pairs associated with them, otherwise I'd just make a column.

Give a table for products and a table for key-value pairs, I want to know what kind of indexes to set up to best support this.

Tables:

  • Products: productId(pk), name, category
  • ProductDetails: productId(fk), name, value(text)

Frequently used queries I want to be fast:

  1. SELECT * from ProductDetails pd where pd.productId = NNN
  2. SELECT * from ProductDetails pd where pd.name='advantages' and pd.value like '%forehead laser%`
Eric Jorgensen
  • 1,682
  • 2
  • 14
  • 23
  • 2
    While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;) – Yitzhak Khabinsky Jun 15 '21 at 19:17
  • An index on pd.productId & pd.name. For the pd.value as you are doing a like an index wouldn't be much used unless it is a full text index. – Dijkgraaf Jun 16 '21 at 00:43

1 Answers1

0

I'd encourage you to comb through this answer: How to create composite primary key in SQL Server 2008

This all depends on your querying and data constraint needs. You can have a clustered index on just the productId and add multiple non-clustered indexes on other composite keys (ProductDetails.name and .value and/or productId too). These can also enforce the uniqueness of the data being inserted so you don't get duplicates.

Be aware though there are diminishing returns on adding too many indexes on large tables where inserts/updates need to occur as well. The db has to determine where the row should go in relation to each index.

Grant Lindsey
  • 95
  • 2
  • 9