indexing questions

chand012 · Post by **chand012** » Wed Jun 20, 2001 11:32 am

1) Is a normal index on a varchar field of any use if you are only doing likep queries on the field and not sorting on it (and you have a metamorph inverted index on the field)?

2) The documentation refers to "not indexing small amounts of data as doing so may slow down searches due to the overhead of looking in an index". Can you give any rules of thumb on this? I'm wondering whether it's still a good idea to have a unique index on a primary key counter field for a small table, if the counters are generated by the system and inserts are rare.

Post by **mark** » Wed Jun 20, 2001 12:18 pm

likep will only use a metamorph or metamorph inverted index, not a regular index.

Technically you shouldn't need a unique index on a counter field to keep it unique. But I often like to have one to prevent mistakes in applications where records might be copied or such. And as long you have an index there's no harm in making it unique.

chand012 · Post by **chand012** » Wed Jun 20, 2001 12:50 pm

Thanks. I understand your point about unique indexes. However, if I have a small table, and I otherwise wouldn't have an index on the primary key counter--and I'm not worried about manually duplicating counter values--is there a performance issue in having the unique index?

Post by **mark** » Wed Jun 20, 2001 1:39 pm

There will be a small speed penalty for table inserts/deletes/updates. There will be no penalty for selects unless the table is only a few records and you are searching on the indexed field.