MATCH (m:Product) WHERE 'moisturize' IN m._effect RETURN m
In this query, '_effect' is a property of an array. When the query above returns non-empty results, it takes only 2ms, shown in the browser. And when the result set is empty, it takes over 100ms to complete. I already created on the array property '_effect'. Why doesn't the index have an effect on the IN keyword in the query, when the query returns nothing?
However, Profile shows that:
In my database, when result is empty, it did 84409 db hits for NodeByLabelScan for 84408 estimated row, then another 84408 hits for the IN filter.
When the result is not empty, the number of db hits are the same, 84409 and 84408. The difference is this number:
Cypher version: CYPHER 4.0, planner: COST, runtime: INTERPRETED. 168817 total db hits in 8 ms. Cypher version: CYPHER 4.0, planner: COST, runtime: INTERPRETED. 168817 total db hits in 109 ms
So, the number of DB hits are the same, but the time to complete is very different and this is shown in the browser: 8 VS 109. Why is that?