Which query will be faster?

[Faiz]

Hi,
Which one of the following query will be faster in terms of performance,
1) select * from html
where Title\Description\Keywords\Body likep $query
and (ProductId in ($prod))

Then display the results.
If there are 100 rows in the table, then $prod will not be less than 90. (meaning, it will almost be a full table scan with an extra overhead in the query)

OR

2) select * from html
where Title\Description\Keywords\Body likep $query

Then display the results after filtering out only those results which belong to $prod.
My guess is, the 2nd query will be better (without any overhead). I hope I am able to explain what I mean.
Regards,

[John]

In the first case Texis will do the LIKEP first, and then look at the records which match $query to see if they match the rest of the WHERE clause, which is what you are proposing in the second case. The first query is likely to be faster, as the filtering will happen as soon as possible.

If you have a Metamorph compound index that includes ProductId then it can be even faster, as it can resolve the entire query with one index.

[Faiz]

Thanx. Will the first query be still better if I have a sub-query in place of $prod, for example,
select * from html
where Title\Description\Keywords\Body likep $query
and (ProductId in (select ProductId from products
where RestrictedFlag=0))

Right now, I have a metamorph inverted index on (Title\Description\Keywords\Body,ProductId). How different is compound index from inverted index? How can I create a compound index? In case of a compound index, do I have to use LIKE instead of LIKEP? Will it affect the search results in terms of relevant documents?
p.s. thanx for the prompt reply.

[John]

That is already a compound index, which includes ProductId as well as the text fields.

As long as the subquery is efficient then that is a good way to write the query, and Texis will do the required operations in the best order.