Re: Indexing strategy

On 04/17/2011 03:52 AM, Phoenix Kiula wrote:
> Hi,
>
> Need some help here. I have a large table (200 million rows already).
>
> There are two columns.
>
> ColA
> ColB
>
> There is an index on ColA. It's an important column.
>
> ColB is a boolean. Either 1 or 0.
>
> For about 10% of the data, ColB is 1. Otherwise it's default is 0.
>
> Now, my question: for that 10%, is it worth adding a conditional index
> on "ColA WHERE ColB = 1"? Will this save time for the queries that are
> related to 10% of my data where the ColB is 1? Or will the main
> leading index on ColA already speed things up as much as it can?
>
> Thanks for any ideas!
>
> PK
>
I'll take a wild guess:

it depends on your data:

if colA is highly selectable (ie unique or very nearly so), then it wont help.

if colA is much less unique, and adding colB = 1 will drop the dup's a lot, and you'll always frame query query as "ColA = ?? and colB = 1", then it'll help.

said another way, if index on colA gets you very close to what you need, then index on colB wont help. If index on colA still has a lot of similar results, then you gotta ask yourself: will very few have colB = 1? If yes, then add the index.

-Andy