Performance when Index has no match

[former_SQLA_member1694868]

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?

[VolkerBarth · Accepted solution]

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.