Re: Correct syntax to create partial index on a boolean column

On 15 Dec 2011, at 5:43, Mike Christensen wrote:

> For the boolean column Foo in Table1, if I want to index all values of
> TRUE, is this syntax correct?
>
> CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo;
>
> The query:
>
> SELECT * FROM Table1 WHERE Foo;
>
> should use the index, and:
>
> SELECT * FROM Table1 WHERE NOT Foo;
>
> should not, correct?

Correct, but...
That's not a particularly useful index to create. That index just contains values of true where the associated column equals true - you're storing the same information twice.

It's generally more useful to index a column with values that you're likely to be interested in for limiting the result set further or for sorting or some-such, as long as the operation performed benefits from using an index.

From your later example, for instance:

SELECT RecipeId from RecipeMetadata where diet_glutenfree;

If you plan to use this query in a join, an index like this would be more useful:

CREATE INDEX recipemetadata_recipeid_glutenfree_idx ON RecipeMetadata(RecipeId) WHERE diet_glutenfree;

That's a bit similar to creating an index on (RecipeId, diet_glutenfree), except that the latter also contains entries that are not gluten-free of course.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.