Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Using '<>', IS NULL, and indexes

    So I ran the following:

    Code:
    SELECT * FROM ua_agencys WHERE city = 'CORVALLIS';
    the index on city was used

    Code:
    SELECT * FROM ua_agencys WHERE city <> 'CORVALLIS';
    the index on city was not used

    Code:
    SELECT * FROM ua_agencys WHERE city = 'CORVALLIS' OR city IS NULL;
    the index on city was not used

    Code:
    SELECT * FROM ua_agencys WHERE city <> 'CORVALLIS' OR city IS NULL;
    the index on city was not used

    Code:
    SELECT * FROM ua_agencys WHERE city = 'CORVALLIS' OR city IS NOT NULL;
    the index on city was used

    Are there rules that indicate that a <> expression will avoid the use of indexes, as will a search for NULL values? We're trying to build a best policies document, and I came across this.

    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    NULL values are not stored in the index, so the optimizer sees no reason to look for them there. Similarly, when you're searching for " * WHERE something <> 'SOMETHINGELSE' " you're essentially trying to retrieve the entire table, minus few rows where "something" does equal 'SOMETHINGELSE'. In that case it is often more efficient to sequentially read the whole table directly (unless the SELECT list contains only the fields that can be found in the index itself, in which case I assume the index will be chosen).

Posting Permissions

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