CODEWITHSUNDEEP
sqlsql-serverperformanceoptimizationindexing
rickythefox
rickythefox

Reputation: 6851

Indexing a table for better performance by a query

What index/indexes would you recommend to be created on the table for making the following type of query more efficient:

SELECT MAX(val1)
FROM table
WHERE val2 = 'x' OR val3 = 'y'

x and y are of course variable. val2 and val3 are almost always unique, some duplicates may occur.

Upvotes: 2

Views: 148

Answers (1)

KM.
KM.

Reputation: 103589

have an index on val2+val1 and another on val3+val1 and write query like:

SELECT MAX(val1)
FROM (SELECT max(val1) FROM table where val2 = 'x'
      UNION ALL
      SELECT max(val1) FROM table val3 = 'y'
     ) dt

Upvotes: 4

Related Questions