Быстрый поиск в больших базах

[AlexSuv]

Имеется задача обеспечение быстрого поиска в базе (interbase firebird 1.5), размер базы 0,8 ГБ
главная таблица по которой необходимо произвести поиск содержит около 500 тыс. записей.
Поиск нужно производить по полю типа текстовый BLOB, информация в котором сжата gzip-ом.
Размер текстовой информации в одном поле колеблется от 200 байт (в среднем) до 65 кб (максимум).

Поиск должен проводится обязательно по всему контенту.

Мои варианты решения задачи:

1). Создание отдельного несжатого поля в которое будет хранить все слова в одном экземпляре,
содержащиеся в поле соодержащем контент. Затем поиск по этому поля простой функцией like '%фраза%'.

плюсы: неплохая скорость построения полного индекса (около 9 мин.) или 1 мс на одну запись
минусы: достаточнао медленная скорость поиска (т.к. приходится перебирать все записи ибо в interbase по блоб и большим текстовым полям индекса не построишь)
размер индекса около 400 мб.

2). Создание отдельной таблицы для индекса со структурой (WordName varchar(50),Data blob), где
WordName - уникальное слово
Data - блоб поле, содержащее список всех записей, в которых упоминается данное слово

плюсы: скорость поиска - почти мгновенная
минусы: индекс строиться очень долго (около двух часов) и съедает огромное количество памяти

размер индекса около 700 мб!

3). Похожее на 2, только создается две таблицы WordsIndex (Id int,WordName varchar(50)) и TopicIndex (WordId int,TopicNo int), где

WordsIndex:
Id - уникальный номер уникального слова в таблице
WordName - уникальное слово

TopicIndex:
WordId - ссылка на слово в таблице WordsIndex
TopicNo - соответственно номер записи, где это слово встречается

плюсы: минимум использования памяти, скорость поиска - почти мгновенная
минусы: время на создание полного индекса - несколько часов, размер индекса более 1 гб., кол-во записей в таблице TopicIndex - 30 млн. !!!!, со всеми вытекающими отсюда последствиями по скорости добавления следующих данных

собственно вопрос, а какой вы бы предложили оптимальный механизм поиска для таких условий.
Возможно база баольшей не будет, но чем черт не шутит не хотелось бы чтобы при 1 млн. записей поиск просто умер.

Оптимальное время поиска, как для меня - не более 10 сек. и размер индекса не более 50% от размера базы

[hvlad]

3). Похожее на 2, только создается две таблицы WordsIndex (Id int,WordName varchar(50)) и TopicIndex (WordId int,TopicNo int), где

WordsIndex:
Id - уникальный номер уникального слова в таблице
WordName - уникальное слово

TopicIndex:
WordId - ссылка на слово в таблице WordsIndex
TopicNo - соответственно номер записи, где это слово встречается

Если слово в записи повторяется несколько раз, сколько раз оно встретится в TopicIndex ?

плюсы: минимум использования памяти, скорость поиска - почти мгновенная
минусы: время на создание полного индекса - несколько часов,

Это с учётом распаковки блоба ? Или это скорость вставки 500К записей (т.е. с учётом упаковки ) ?

размер индекса более 1 гб., кол-во записей в таблице TopicIndex - 30 млн. !!!!

30 000 000 / 500 000 = 60 т.е. это, скорее всего, уникальные вхождения слов в записи, хорошо.
Слова, короче чем 3-4 символа обычно не индексируются

со всеми вытекающими отсюда последствиями по скорости добавления следующих данных

А какие последствия ??? Это предположение или где ?

не хотелось бы чтобы при 1 млн. записей поиск просто умер.

500К записей у тебя уже есть. Что мешает проверить на 1000К ?

[AlexSuv]

3). Похожее на 2, только создается две таблицы WordsIndex (Id int,WordName varchar(50)) и TopicIndex (WordId int,TopicNo int), где

WordsIndex:
Id - уникальный номер уникального слова в таблице
WordName - уникальное слово

TopicIndex:
WordId - ссылка на слово в таблице WordsIndex
TopicNo - соответственно номер записи, где это слово встречается

Если слово в записи повторяется несколько раз, сколько раз оно встретится в TopicIndex ?

все зависит от того, в каких топиках это слово встречается (макс было 150 тыс раз!!!)

плюсы: минимум использования памяти, скорость поиска - почти мгновенная
минусы: время на создание полного индекса - несколько часов,

Это с учётом распаковки блоба ? Или это скорость вставки 500К записей (т.е. с учётом упаковки ) ?

Это даже баз распаковки, т.к. пока данные несжаты но все равно это очень долго

размер индекса более 1 гб., кол-во записей в таблице TopicIndex - 30 млн. !!!!

30 000 000 / 500 000 = 60 т.е. это, скорее всего, уникальные вхождения слов в записи, хорошо.
Слова, короче чем 3-4 символа обычно не индексируются
количество цникальных слов около 800 тыс.

со всеми вытекающими отсюда последствиями по скорости добавления следующих данных

А какие последствия ??? Это предположение или где ?

последствия вставки в таблицу TopicIndex с 30 млн. записями, двумя
индексами и проверкой уникальности вставленных записей

не хотелось бы чтобы при 1 млн. записей поиск просто умер.

500К записей у тебя уже есть. Что мешает проверить на 1000К ?

если на 500 тыс. так тормозит, что говорит про 1 млн., хотя у меня
машина неплохая (P4 3000, 1Гб ОЗУ, 200 гб 7200), у клиента вообще
умрет

[Ivan_Pisarevsky]

на 500 тыс. так тормозит, что говорит про 1 млн., хотя у меня
машина неплохая (P4 3000, 1Гб ОЗУ, 200 гб 7200),

перфмон что говорит на счет очереди к диску? я почти уверен диск затыкается по самое нехочу. Один САТА винт и база в пару гиг на нем смотрится блекло...

[hvlad]

со всеми вытекающими отсюда последствиями по скорости добавления следующих данных

А какие последствия ??? Это предположение или где ?

последствия вставки в таблицу TopicIndex с 30 млн. записями, двумя
индексами и проверкой уникальности вставленных записей

Я не понял - что тормозит-то ?
Ты каждый день перестраиваешь индекс или заливаешь по 500К записей ???