multiple LIKEP's against the same column

[kevin12]

If my query looks like this:

select * from table
where
body\category\context LIKEP $BODY_QUERY
and body\category\context LIKEP $CONTEXT_QUERY

does the query optimizer combine the two constraints into a single LIKEP constraint, in which case if the two queries together contain 5-9 terms then only results which match at least two of the terms will be returned?

Or is it treated as two completely separate constraints which just happen to use the same index?

[bart]

They are treated as separate queries, the optimizer will only try to organize the 'and' operation to make it faster.

The real problem in doing two likep-s in this manner is that both will only return at most LIKEPROWS worth of results to be anded together. The result will not be predictable.

The proper way to do this is to have only one likep and use a standard like for the second field:

select * from table where
body\category\context LIKEP $BODY_QUERY
and body\category\context LIKE $CONTEXT_QUERY