2

Suppose there is a table with non-primary key columns (A,B,C,D) of which there is an index on (A,B,C,D)

Will queries involving a subset of (A,B,C,D) utilise the index? What sort of idiosyncrasies exist with partial key lookups as described?

I am specifically interested in how this works in SQL Server, Sqlite, Firebird.

Herman Schoenfeld
  • 8,464
  • 4
  • 38
  • 49

2 Answers2

5

Queries that use an initial subset of the index should use the index.

So, equi-joins on (A), on (A) and (B), and on (A) and (B) and (C) should use the index.

Actually, you can add an inequality as well, so, the index should support all the following:

  • x.A = y.A
  • x.A = y.A and x.B > y.B
  • x.A = y.A and x.B = y.B
  • x.A = y.A and x.B = y.B and x.C > y.C
  • x.A = y.A and x.B = y.B and x.C = y.C
  • x.A = y.A and x.B = y.B and x.C = y.C and x.D > y.D
  • x.A = y.A and x.B = y.B and x.C = y.C and x.D = y.D
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • what about x.C = y.C? Will that result in a full table scan? – Herman Schoenfeld Mar 25 '13 at 01:34
  • 1
    That should result in an index scan rather than a table scan. It's faster than a table scan but not as fast as an index seek like the scenarios Gordon listed above. There's more information [here](http://stackoverflow.com/questions/1136524/sql-server-plans-difference-between-index-scan-index-seek). – Ed Gibbs Mar 25 '13 at 02:05
3

Yes, if the search keys are in the same order as the index fields.

Otherwise no. Say you are given instructions to go to the library, look in "The Great Gatsby", Chapter 3, the third page. That would be like using a multi-field index.

Now say, you are told to get Chapter 3, the third page....without the book title. You'd have to look in every book.

Steve Wellens
  • 20,506
  • 2
  • 28
  • 69