I have a table with 6.6 million rows and 17 columns.

The only selects from this table use 3 columns as criteria:
GroupID integer

SysYear integer

Account varchar(50)

I have an index setup that is the combination of the 3 columns. These columns are not part of any other indexes.

The primary factor in the search is whether the account number is found, the GroupID and SysYear are just to limit the scope of the search.

When I select with "good" account numbers the results are nearly instantaneous.

A select with a "bad" account number takes anywhere from 4 - 15 seconds even though the execution time lists .125 seconds to .281 seconds.

My question is based on my rudimentary understanding of indexes: that they make retrieval and ordering faster because the criteria can be evaluated without accessing the actual rows. If that is incorrect that may affect the question.

Is there any way to make these queries deliver their results (or lack of results) faster where there is no match?

asked 09 May '13, 17:46

Siger%20Matt's gravatar image

Siger Matt
accept rate: 15%

To cite from the docs on composite indexes (i.e. those made of more than one column):

When you create composite indexes, you should think carefully about the order of the columns. Composite indexes are useful for doing searches on all the columns in the index or on the first columns only; they are not useful for doing searches on any of the later columns alone.

If you are likely to do many searches on one column only, that column should be the first column in the composite index. If you are likely to do individual searches on both columns of a two-column index, you may want to consider creating a second index that contains the second column only.

So, if I do understand your requirements correctly, then using a separate index on account or using account as the first column of a combined index should speed up your queries.

permanent link

answered 09 May '13, 17:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 May '13, 17:46

question was seen: 2,004 times

last updated: 09 May '13, 17:57