Indexing SQL for Between query with only one match

问题:

We have a table with more than two million rows where all queries against it will be a Between lookup using Column1 and Column2. Also, there will only be one possible result. For example...

Col1     Col2
1        5
6        10
11       15

select * from table1 where 8 between Col1 and Col2

I currently have an unique clustered index on Col1 and Col2. So far I have been unable to figure out how to further tune the query and the indexes to minimize the rows handled. The execution plan currently reports cost of almost 0.5 and 113k rows handled when locating the one and only correct answer.

What options might I be overlooking?

As requested, some details from the current execution plan:

Operation
 Clustered Index Seek
Predicate
 CONVERT_IMPLICIT(bigint,[@2],0)<=[Col2]
Seek Predicate
 Seek Keys[1]: End: Col1 <= Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

回答1:

Are the ranges always non-overlapping? You mention that there is always only one match. If they are, you can write it as:

SELECT * FROM table1 
   WHERE 8 <= Col2 
   ORDER BY Col2 ASC
   LIMIT 1

This will give you the row with the lowest value of Col2 which is above 8 - which is the range you are interested in. The index would only be needed on Col2, and the cost should be small.

Since you did not mention the DBMS you are using, the LIMIT 1 should be replaced with whatever your DB uses to fetch the first N results.

You will have to check Col1 <= your_value in code to ensure that the value you are looking for really is in the range.

回答2:

I think I have found the answer. I had to start by creating an Unique Clustered Index on Col1, then create an Unique Unclustered Index on Col2. The query then had to be updated to force lookups on each Index.

select * from table1 where Col1 = 
    (select max(Col1) from table1 where Col1 <= 8)
and Col2 = 
    (select min(Col2) from table1 where Col2 >= 8)

Execution plan now reports 0.0098 cost and 1 row handled.

回答3:

select * from table1 where Col1 <= 8 and Col2 >= 8

Maybe the "between" with two columns is causing an issue.

Also, you should just have 1 composite index on both columns (Col1, Col2).