Hello.

A friend of my told me that if I create an index to a column, the index is called only if the select matches only one row. In other words if I select using = it works but if I use between not.

Ex. He told me that this select will not use index.

select * from Product where id between 1000 and 2000;

Even if I have an index to id.

I really can't believe. Is he right?

Thanks in advance

No - he is wrong.

It is possible the query won't use an index for that query, and note that an RDBMS can "use" an index in a few different ways but it depends on the specifics.

But he is categorically wrong to say an index is only "used" if the query matches only one row. Ask him then why the engine will let him index non-unique columns?

What is you RDBMS? In some of them you can look at the plans and see how the engine uses the index.

As a general rule he's wrong, or at least too simplistic. The way indexes are used is highly dependent on your DBMS, the nature of the query, the data and the type of index. Perhaps your friend is thinking specifically of hash indexes which only work for comparisons based on equality and not on range queries.

The number of rows returned by a query can also be a factor in whether an index gets used or not. If the query returns a large proportion of the rows from a table then the query optimiser may find it more efficient not to use an index.

Unless it covers the query or is clustered in which case it is likely to find it efficient.

Thank you for all responses