Best practices for refreshing text indexes

[former_SQLA_member1694911]

What are the best practices for refreshing text indexes on SQL Anywhere 11.0.1, or useful criteria to help decide?

We are adding full text search into the next major version of our software, and are debating the wisdom of different text index refresh approaches.

• We will, eventually, have a large number (for big installations, millions) of text item on the order of 100 – 300 bytes each.
• Mostly, they will be added/modified/deleted at human speed, but a couple of subsystems could potentially add them dozens of times a minute.
• After adding/changing a text item, users will expect to be able to search for them either immediately or within a minute or two.
• Multiple users can apply full text search filters concurrently; while in use those queries will refresh one to several times a minute, but usage of these queries will be spiky. We don't know yet if queries will be done a few times a day by a few people or throughout the day by a bunch of people.

We're looking for guidance on how to decide among IMMEDIATE REFRESH, AUTOMATIC REFRESH EVERY 1 (or 2) MINUTES, MANUAL REFRESH just before each query, etc. Things that might help are:

• Can IMMEDIATE REFRESH "know" to only update for the new/changed text items? How about MANUAL and AUTOMATIC?
• Are MANUAL and AUTOMATIC REFRESH fast when nothing has changed?
• Any other factors we'd want to consider?

Thanks VERY much,
Dan

[Former Member · Accepted solution]

IMMEDIATE REFRESH text index only updates the data that has changed. MANUAL or AUTO REFRESH text indexes may need to be rebuilt from scratch from time to time. When this happens, the old copy of the index is available to the users for the duration of the rebuild.

When no data has changed, MANUAL and AUTO REFRESH run relatively fast.

IF the users will expect the newly inserted items to be visible in the full text search immediately, IMMEDIATE text index may be a good solution, however, one thing to keep in mind is that all the updates to the indexed values will become somewhat slower.

Just to clarify - a reasonable number of small documents added to an IMMEDIATE TEXT INDEX a minute on a not overly busy server should work well.

[former_SQLA_member1694911 · Accepted solution]

Thank you, Elmi!