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

asked 04 Feb '11, 01:36

Dan%20Konigsbach's gravatar image

Dan Konigsbach
455101220
accept rate: 0%


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.

permanent link

answered 04 Feb '11, 20:04

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

edited 04 Feb '11, 21:49

Thank you, Elmi!

(05 Feb '11, 01:10) Dan Konigsbach
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15
×3

question asked: 04 Feb '11, 01:36

question was seen: 734 times

last updated: 04 Feb '11, 21:49