Choosing Manual vs. Automatic Index Optimization

Whether to optimize Metamorph indexes manually (via a SQL statement) or automatically (via chkind) depends on the nature of table changes and searches.

Deployments where table changes occur in batches, and/or search load predictably ebbs and flows, are good candidates for manual optimization. The optimizations can be scheduled for just after the batch table updates, and if possible when search load is low. This will keep the index(es) up-to-date (and thus performing best) for the longest amount of time, while also avoiding the performance penalty of updating both the table and the index simultaneously. Optimizing at off-peak search times also improves peak-load search performance by freeing up resources during the peak. Contrast this with automatic optimization, which cannot know about upcoming table updates or search load, and thus might trigger an index update that coincides with either, negatively impacting performance.

Applications where tables are changed at a more constant rate (e.g. a steady stream of changes) may be better candidates for automatic updating. There may not be any predictable "best time" to run the optimization, nor may it be known how much the indexes are out-of-date. Thus the decision on when to optimize can be left to chkind's automatic out-of-date scan, which attempts to minimize both staleness of the index and frequency of index optimizations.

Some situation may call for a combination, e.g. chkind to handle miscellaneous table updates, and an occasional manual optimization after batch updates, or just before peak search load.