Thunderstone Support Forums

The sql query without any or with one AND clause takes less time than the one with two AND clauses. I was wondering, if its possible to speed it up. Here are my observations,
1) <sql max=10 "select field1,field2,... from documents
where Title\Description\Keywords\Body likep $query">
This takes 0.07secs.

2) <sql max=10 "select field1,field2,... from documents
where Title\Description\Keywords\Body likep $query
and Doc_Flag=0">
This takes 0.08 secs.

3) <sql max=10 "select field1,field2,... from documents
where Title\Description\Keywords\Body likep $query
and (Doc_Flag=0 and Product_Flag=0)">
This takes 0.14secs.

I have a metamorph inverted compound index on Title\Description\Keywords\Body, Doc_Flag, Product_Flag. I was wondering, what is causing the 3rd query to take more time. Are the queries working at its optimized level? Is it possible to increase the speed of the 3rd query? I also have to add an OR clause in the query.

Thanks,

There are some shortcuts that Texis can take advantage of when looking in the index to resolve a single additional clause from the compound index, that are not available with multiple clauses. If possible put the most restrictive of the two ANDs first, and it will prevent the other from being evaluated unless it is true.