June 27, 2013 at 7:22 am
Hey,
I have a query that goes along this line:
SELECT * FROM myTable
WHERE col1=1 AND col2=2
ORDER BY col3, col1 DESC
What's the best index for this query?
My guess is col3 , col1 DESC , col2
June 27, 2013 at 8:30 am
Easily tested:
/*
Use a tally table as a row source
DROP TABLE #MyTable
SELECT
col1 = ABS(CHECKSUM(newid()))%32,
col2 = (10000-n)%32,
col3 = n,
col4 = newid()
INTO #MyTable
FROM InlineTally (1000000)
*/
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
-- no indexes
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 3, logical reads 5588, physical reads 38, read-ahead reads 5267.
-- Table scan
CREATE CLUSTERED INDEX cx_blah ON #myTable (col1, col2)
DBCC DROPCLEANBUFFERS
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 1, logical reads 4, physical reads 1, read-ahead reads 0
-- clustered index seek
DROP INDEX [cx_blah] ON #myTable;
CREATE CLUSTERED INDEX cx_rvl ON #myTable (col3, col1 DESC, col2)
DBCC DROPCLEANBUFFERS
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 3, logical reads 5707, physical reads 0, read-ahead reads 6
-- clustered index scan
-- missing index warning
DROP INDEX [cx_rvl] ON #myTable;
CREATE UNIQUE CLUSTERED INDEX cx_blah2 ON #myTable (col3)
CREATE NONCLUSTERED INDEX ux_blah3 ON #myTable ([col1],[col2]) INCLUDE ([col3])
DBCC DROPCLEANBUFFERS
-- useful non-clustered index
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0
-- index seek (ux_blah3)
CREATE NONCLUSTERED INDEX ux_rvl ON #myTable (col3, col1 DESC, col2)
DBCC DROPCLEANBUFFERS
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0
-- index seek (ux_blah3)
DROP INDEX [ux_blah3] ON #myTable;
DBCC DROPCLEANBUFFERS
SELECT col1, col2, col3
FROM #myTable
WHERE col1 = 1
AND col2 = 2
ORDER BY col3, col1 DESC
-- Scan count 1, logical reads 3232, physical reads 19, read-ahead reads 3170
-- index scan (ux_rvl)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 27, 2013 at 8:40 am
Will test, thanks!
June 27, 2013 at 8:48 am
Indexes in most cases should be primarily there to support the WHERE clause, if they can support the WHERE and the ORDER BY, that's a bonus.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply