3.4 Choosing the appropriate indices

The goal behind indexing is to improve search performance. To do that it is important that the right index is used at the right time. Although Texis does try and use the appropriate indices there are several hints and clues that you can use to guide it. The most obvious first requirement is that the right index must exist. For the LIKE family of searches that would be a Metamorph or Metamorph inverted index. For other search types that would be an ordinary index, optionally unique. If multiple indexes exist that could be used, then the index which will resolve the most of the where clauses is chosen, otherwise the choice is currently undefined.

A second goal is to use as few indices as possible. If you have fixed length fields that are commonly searched along with full-text searches it may make sense to create a compound metamorph index. These indexes contain both the full-text information, as well as additional fields. These indices can also help with ORDER BY. Another way to reduce the number of indices used to resolve a query is through the use of "virtual" fields. The virtual field operator '\' concatenates strings together with a newline separating fields. This can often be used when you have several text fields, and you want to search all of them, either for the same or different queries. When ranking, due to lead bias, you should put either the most important, or the displayed fields first in the list, although they should generally be the same.

Back: Joins and subqueries

Next: Metamorph Queries