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
equalstrue - 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
setfurther 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
thatare not gluten-free of course.
Alban Hertroys
--
The scale of a problem often equals the size of an ego.