CONTAINS - Single v Multiple performance

Posted by danielb on 11-Dec-2017 17:15

I have a quick question regarding the CONTAINS operator.

Is there any performance/efficiency benefits using the "&" operator inside the criteria, v multiple CONTAINS clauses on the same query?

For example:

FOR EACH table
    WHERE table.field CONTAINS "alpha & beta & gamma"
    :

v

FOR EACH table
    WHERE table.field CONTAINS "alpha"
    AND table.field CONTAINS "beta"
    AND table.field CONTAINS "gamma"
    :

The xref shows that the latter uses multiple indexes (all the same index), whereas the former uses a single index. I've run some performance comparisons, and the timings are almost identical (+/- some statistical differences), so I'm comfortable that there is no material performance difference, but wanted to be certain.

I have found that the order of the terms inside of the CONTAINS operator make a difference, so if the least common word is first, then the query executes much quicker than if the most common word was first. I remember seeing a community post/KB about this, but can't find it at the moment.

All Replies

This thread is closed