Creating the right indexes

[tony.malandain]

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.

[mark]

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.

[tony.malandain]

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.

[mark]

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.