I'm not the expert on indexes really - I'm trying to work my way through a few materials now.

Anyway, let's say I have a table with 7 million records spanning 3 years. However the most common query will pull say the last 3 months -- using an integer field (unix epoch time).

This 3 month query usually takes about 18 seconds --- but many other queries may be built atop it, so I was trying to speed it up. I created a clustered index on the unix_epoch_time int field that would eventually be in a 'where' statement filter.

However, this doesn't appear to have sped up the query time at all. Does this mean this clustered index is a spurious waste? Or accounts for something like 1% of the query time? I thought it would be much faster.

  • 1
    Can you Paste The Plan? Might be a little more helpful to find the query you're running. brentozar.com/pastetheplan – John Oct 31 '17 at 19:43
  • Query and index tuning involves a number of trade-offs and you probably need non-clustered indexes too. I suggest you add the table DDL to your question along with an example query and link to actual execution plan uploaded to brentozar.com/pastetheplan. – Dan Guzman Oct 31 '17 at 19:44

You ask if the clustered index is a waste: ideally a clustered index is unique, relatively small and does not change once created (see https://www.red-gate.com/simple-talk/sql/learn-sql-server/effective-clustered-indexes/ ) Your integer field is certainly small but my guess is it's not unique, although it's not possible to tell from the question.

To answer the question you could use an IDENTITY(1,1) as your clustered index key, then create a non-clustered index using your Unix time field. To make the queries faster you can INCLUDE commonly used columns in the non-clustered index, then if queries are covered by the index they need not even visit the main table.

Also if your last 3 months data could be defined in terms of particular date ranges, you could even build a filtered index to limit the size of the index and speed the searches. Based on experience with tables of 1m+ rows, if you are in control of the indexing you should be able to satisfy most queries in less than a second.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.