Covered index with WHERE and IN clause

SQL++
1

Hi, I want to know if this is a normal behaviour:

I have a index like:
CREATE INDEX idx_family_cond ON CONTENT((main.family)) WHERE ((type = “animal”) and ((main.species) in [“mammal”, “reptiles”, “insects”])) WITH { “num_replica”:1 }

then, as I understand, this query should be covered by this index:
select main.family
from CONTENT use index (idx_status_cond)
WHERE (type = “animal”) and (main.species) = “mammal”

but it isn’t, and only when I add all main.species is when I get a cover:

select main.family
from CONTENT use index (idx_status_cond)
WHERE ((type = “animal”) and ((main.species) in [“mammal”, “reptiles”, “insects”]))

Is this normal? Logic says both should be cover

2

  1. First query to pick the index query must have type = “animal” predicate. Otherwise index doesn’t have all the info and query will not choose that index

  2. Query must have predicate on main . species with any values that specified in the index

  3. Query must have predicate on leading index key.
    Example:

    SELECT main.family
    FROM CONTENT
    WHERE type = “animal” AND main.species = “mammal” AND main.family IS NOT NULL;

  4. Covering query, the predicates are re applied as index WHERE condition has multiple values on main.species . The condition must exactly present in the query WHERE OR main.species must be part of index keys.

1 Like