Thunderstone Support Forums

Can you suggest a better way of being able to sort by this field without having it in the index? Should I create a separate index for this field?

I created a metamorph inverted index without the SiteURL field in it and the query works fine when ordering by SiteURL. Is the benefit of having it in the index to mainly speed up the query?

The benefit of having data in the metamorph index is to speed up the ORDER BY by having the data to hand while searching the index, rather than needing to read all the matching records to get the sort keys. By having large fields in the index you would be reading the additional data for all text searches, and probably excessive data for most of the searches ordered by SiteURL. For that reason the fields are intended to be used for small fixed size data items such as dates and integers, and it will display a warning message about varchars.

Is there an alternate way of sorting by SiteURL instead of including it in the metamorph index?

Texis will still do the ORDER BY if you specify it. You might want to consider putting the Body data in a BLOB, which may reduce the size of the table so that you regain most of the efficiencies of only reading small amounts of data.

I'm trying to find a way to still sort on SiteURL and include it in the index to speed up the query. Is it possible to include a partial field in the metamorph index, maybe including the 1st 10 characters of the SiteURL?

Defining the field as varchar(10) will include the first 10 characters or so in the index, and it will sort on those 10 characters.

In your previous message, you mentioned that putting the Body data in a BLOB would help. I'm already putting that field in a BLOB. Did you mean to put the SiteURL in a BLOB?

When I had the SiteURL as a shorter field, I had problems updating data in that field if it was longer than the defined length. Is this supposed to happen?

No, I didn't know the Body was already a BLOB. SiteURL should not be a BLOB.

There shouldn't be any problems updating data that is longer than the defined length. What sort of problems did you see?