Re: Composite index question

On Jan 13, 3:49 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> nickli2..._at_gmail.com wrote in news:4800fb42-4248-4184-bb4b-6108db8579d0
> @m34g2000hsf.googlegroups.com:
>
> > my queries often select two leading columns
>
> HUH?
> What are "two leading columns"?

I think the OP just means the query has the value of the first two columns defined to the table. Where the columns are located relative to each other in a row makes no difference.

If you have several queries that go against a table and the query has multiple column comparisons in the where cause such as where col1 = var1
and col2 = var2
then yes a single composite index would be better than two separate single column indexes.

You want to select your indexes (other than PK and perhaps UK) based on what columns are available to your queries . You may find you have several queries that know both columns and a few that have only one. To support the queries with only one column of the two you would most likely want to place that column first in the composit index. If you have some queries that use only one column but both column are used by a couple of queries then you still may need two indexes but one will be composit index to support the queries that provide both columns and the other will be a single column index on the trailing column in the composit index to support those queries that provide only this value.

I hope my description is clear.

• Mark D Powell --