Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Between and indexes

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by santana.beto View Post
    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.
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas View Post
    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.

  5. #5
    Join Date
    Apr 2010
    Posts
    2

    Thank you very much

    Thank you for all responses

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •