Merging Indexes - Doing my head in...

We agree that merging indexes is a good solution, and we've got someone working on it. (Our recent hire Dr. Peter Venable: http://www.logos.com/press/releases/oracle-senior-application-developer-moves-to-logos-bible-software)

So far it looks like we'll be able to merge a supplemental index into an existing index in half the time it takes to reindex. Peter hopes to have this shippable within a month or so. (We didn't do this in the first release because we didn't want to invest the coding time, and in the early days we were constantly changing the logic of the indexing system, which always requires a full rebuild anyway.)

Unfortunately half is still a long time for large indexes. But it's a hard problem -- you're building a indexes in the gigabytes.

While we do use SQL for a few things, we don't use it to store the "postings" file of the inverted index for the text. (Nobody does.) SQL is designed for databases that are constantly updated, and full-text indices are more often static. Moreover, they're huge and read in a linear fashion; using custom data stores reduces the page overhead of SQL, allows you to do run-compression for lists of hits, etc. (When the word "the" appears every 40-50 characters, you can do cool things like using less than a whole byte to store just the offset from this occurrence to the next. In many cases you can use 1-2 bytes, or less, to store a hit, where in SQL and a full database you'd be storing a large record of many fields, with 32-bit numbers, etc. Define a SQL record to represent a single occurrence of a word, and you'll see it uses many more bytes than the word itself.)

Database optimization is important, and we'v got someone doing that now on our intranet and extranet, to speed customer record lookups, upgrade pricing calculations, and even our data synchronization back-end.

If you're curious about this area, "Managing Gigabytes" by Witten, Moffat, and Bell is a great technical overview. I liked it a lot, and have found many papers by the authors to be very useful. I even visited Moffat in Australia and spoke to one of Witten's graduate student seminars in Waikato, New Zealand.

(Yes, I'm over-answering the question and showing-off. :-) But I want to make the point that we are "all over this problem" and working hard and smart to address it as best possible. And I really like any excuse to get into geeky detail, so feel free to keep the questions or suggestions coming. We're not offended in the least, and we know we've still got lots to learn -- part of the reason we are hiring people like Dr. Venable.)