Thunderstone Support Forums

Hi,
I have the following table:
DocId => char(50)
PageIndex => Integer
Binary => Blob
PlainText => varchar

The table may contain up to 10M lines.
That table is frequently queried to return the most relevant page of a document. I use this query for that:
Select max(rank), PlainText from table where PlainText like 'xxx' and DocId='YYY' group by DocId

The table are also frequently modified: typical changes include:
Delete all records with Id = xxx
Set Id= xxx where Id=yyy
Add new files.

Which indexes would you recommend for that table?

How can I monitor the performance of sql statements to detect which one are costly?

Thanks,
/Tony.

You'll want a metamorph inverted index on PlainText,DocId and a regular index on DocId.

You can use <sysinfo proctime> to measure how long things take.

Thanks,

Yes, we do use likep, but we run the query on multiple DocIds at onces that's why we use the group by...

One question though: If I create the metamorph inverted index on PlainText,DocId will it do a full-text indexing of DocId? We only need a regular index on the DocId.

No. The text index is on the first field. The remainder are aux data put into the index to reduce table accesses when resolving the rest of the query.