Order of Key Columns in Index



  • The execution plan for this query appears to be making a very bad choice (of course I know it is not):

    SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC
    

    The two relevant indexes are:

    CREATE NONCLUSTERED INDEX IX_EMAIL_2 ON EMAIL
    (E_CUS_ID ASC, E_DATE_SENT ASC)
    WITH (FILLFACTOR = 95)
    

    CREATE NONCLUSTERED INDEX IX_EMAIL_3 ON EMAIL
    (E_ID ASC, E_TYPE ASC, E_CUS_ID ASC)
    INCLUDE (E_PER_ID)
    WITH (FILLFACTOR = 95)

    The query plan is choosing IX_EMAIL_2.

    It appears IX_EMAIL_3 is covering and IX_EMAIL_2 is clearly not.

    Is this simply b/c the E_CUS_ID key column is first in IX_EMAIL_2?

    I wasn't aware a completely covering index would be ignored by the engine in favor of an index w/ only one of the needed columns but that column was first in the index.

    NOTE: This table has 121M records so I can't do much testing to see if I'm right without waiting many hours.



  • E_ID is not part of the WHERE clause while index IX_EMAIL_3 is sorted by E_ID so SQL can't seek to any of value from where and need to scan the index to find it. That is why it is taking IX_EMAIL_2.

    Try this:

    CREATE NONCLUSTERED INDEX IX_EMAIL_4 ON EMAIL
    (E_CUS_ID ASC, E_TYPE ASC, E_ID ASC)
    INCLUDE (E_PER_ID)
    WITH (FILLFACTOR = 95)
    

    I think it will be used.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2