|
I have a table with 6.6 million rows and 17 columns. The only selects from this table use 3 columns as criteria: 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? |
|
To cite from the docs on composite indexes (i.e. those made of more than one column):
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. |
