Indexing on one column slows down query, but indexing on two speeds it up



  • For the following query

    SELECT COUNT(*)
    FROM Orders
    WHERE status = 'F' AND priority = 'HIGH';
    

    When I add an index on the column status, the query runtime becomes more than double than if run it without any indexing at all. On the other hand if I put an index on both status and priority like this

    CREATE INDEX order_status_priority ON order(status, priority);
    

    then the runtime becomes around 1/3 of the original runtime without any indexing. Can someone help me understand why this happens? Thanks in advance!



  • It’s unlikely to be any more complex than the following:

    1. No index: the full table must be read to get your result.
    2. Index on just status: SQL Server uses this index to read all the rows one by one that meet the status filter. This can take a long time if there’s a lot of rows that match this filter compared to the overall size of the table. It will not be reading these rows as fast as they would be read using a scan which will read them pages at a time, the index will mean that SQL Server will read the page that a row is on for each row it wants to read - this could mean the same page is read several times.
    3. Index on both columns: only the index needs to be searched, a chunk of the index will be read (just the bits that match your filters). No need to access the rest of the row as you don’t need any other data other than what’s in the index.

    Chances are the optimizer believed that your status filter was better than it really was so was excited to use an index on it.




Suggested Topics

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