|
Задавайте вопросы, мы ответим
Вы не зашли.
Вот, нужно как-то оптимизировать задачу поиска по текстовому массиву (!с учетом веса слова!).
К поиску предъявляются принципиальные условия:
1. Должны учитываться веса слов - они задаются отдельно для каждого документа. Это самое важное требование.
2. Сортировка документов в результате должна происходить по сумме весов найденных слов. (Плюс может добавиться сортировка и по некоторым параметрам документов.)
3. Должна быть возможность поиска в режиме "match all words" - т.е. с обязательным вхождением всех слов.
4. Должна быть возможность поиска в режиме "exclude words" - т.е. с исключением из результатов документов, содержащих заданные слова
В сторону использования FULLTEXT не смотрю, т.к. в постановке задачи имеются дополнительные требования по сортировкам. И нужна возможность гибко управлять весами слов.
Пока самый быстрый вариант у меня получился такой:
1). Тройки вхождения слов (код_документа, код_слова, вес_слова) храним в следующей таблице:
create table search_index(
page_id int not null,
word_id int not null,
weight smalint not null,
index (word_id, page_id, weight)
);
2). По каждому из введенных при поиске слов, делаем запрос к данной таблице, и сохраняем результат в виде пар (page_id, weight) во временной MEMORY табличке:
insert into temporary_memory_table (
select page_id, weight from search_index where word_id='код_введенного_слова'
);
Таких запросов может быть несколько (когда вводится несколько слов).
3). После формирования этой Memory таблицы, делаем к ней запрос с группировкой по документам и суммированием весов найденных слов. И сортируем это дело по полученной сумме весов (ну и еще ограничиваем ее минимально возможным значением).
select SQL_CALC_FOUND_ROWS
page_id, sum(weight) as sumweight
from temporary_memory_table
group by page_id
having sumweight>=100
order by sumweight desc
limit 0,50;
Для того, чтобы это дело работало еще быстрее - в MEMORY таблице имеет ключ по page_id.
Собственно за счет того, что таблица у нас MEMORY, получаем хорошую скорость для простых (2-3 слова) запросов.
Но с увеличением количества вводимых слов, растет и время на вставку и суммирование. Причем не пропорционально.
Как бы это дело оптимизировать? Может я вообще копаю не в ту сторону?
PS: извиняюсь, если слишком озадачил, но уж больно остро стоит вопрос.
Отредактированно Shad (20.10.2007 23:28:10)
Неактивен
Честно говоря, не понял, зачем нужна временная MEMORY-табличка в явном виде,
можно обычным JOIN ведь сделать?
А чем вас FULLTEXT не устраивает? Очень большой разброс весов одного слова по
документам?
Неактивен
Можно чуть подробнее про обычный Join?
Разброс не очень большой, но важна точность. Вес слова, грубо говоря, варьируется в зависимости от местоположения в документе.
Отредактированно Shad (21.10.2007 00:29:34)
Неактивен
select i.page_id, SUM(i.weight) from search_index i join words w using(word_id) where w.data='word1' or w.data='word2' group by 1 order by 2 desc limit 50
Неактивен
Медленно работает на большом количестве слов (10 слов ищет 100 секунд). Но...
Я сейчас, подумал может быть это из-за ограничения на память для временной таблицы? (Процесс большую часть времени висит со статусом "Copying to tmp table on disk").
За это дело какая переменная отвечает? tmp_table_size?
Какое разумное значение туда можно вписать, если на сервере 1GB ОЗУ. Сейчас у меня 33554432.
Отредактированно Shad (21.10.2007 01:01:52)
Неактивен
Пытаетесь написать поисковик? 
Переменная правильная, поставьте значение побольше, например, 200М.
Только учтите, что в Вашей реализации все равно будут большие проблемы
с памятью (представьте, например, 5 одновременных таких запросов).
Неактивен
Пытаюсь. Жизнь заставила.
Поисковик собственно небольшой, отраслевой. И в качестве документа там не web-страницы, а более компактные единицы (по 25-30 слов), но тем не менее, опыта все-равно не достает.
Спасибо вам, что помогаете.
(Кстати, может быть вы занимаетесь и платными консультациями, аудитом?)
Неактивен
Возьмите Яндекс.Сервер, не мучайте себя
Как вариант - возьмите исходники FULLTEXT-индекса, и вставьте туда свою формулу весов слова
в зависимости от положения.
Это будет куда проще и куда быстрее. Как с точки зрения написания кода, так и с точки зрения
поиска.
Неактивен
Сбор данных уже написан. Данные структурированы (штук 20 числовых и текстовых полей на каждый документ). Все разложено по табличкам.
Нужен поиск с разными сортировками (по релевантности, по популярности, по конвертируемости, по возрасту, по доп. флагам).
Я к чему это все? Я просто не уверен, что стандартные решения (Яндекс.Сервер, например) позволят все это сделать. Яндекс.Сервер ведь написан под web-поиск. Там соответствующая формула релевантности и виды сортировок.
А фраза "возьмите исходники FULLTEXT-индекса, и вставьте туда свою формулу весов слова" звучит просто пугающе.
(Пошел читать про Яндекс.Сервер).
Отредактированно Shad (21.10.2007 01:38:09)
Неактивен
Посмотрел исходники FULLTEXT-индекса, там 3000 строк на C, разобраться можно, хотя менять в нем что-то придется, так как он заточен под английский язык. Вам нужно добавить свой алгоритм для весов и убрать те возможности FULLTEXT, которые Вам будут мешать.
Неактивен
Платные услуги оказываем, пишите на sakila@sqlinfo.ru
Неактивен
Мне собственно и нужен английский язык. Но я не cмогу сейчас залесть так глубоко. Хотя наверное, это было бы "по уму" и "на века".
Поэтому...
Возвращаясь к моей беcперспективной реализации...
1. Процесс еще долго висит в статусе "converting HEAP to MyISAM". Это тоже из-за нехватки памяти на таблицы?
2. Плюс еще хотел спросить, может быть MyISAM не самый лучший тип таблиц для моей таблицы search_index?
3. Если все-таки работать со стандартными средствами MySQL, в какую сторону еще можно покопать в целях повышения скорости?
Неактивен
Наверное, MyISAM лучший тип, так как апдейты у Вас редко, а селекты часто. MyISAM будет наиболее быстро работать. Если конечно весь индекс в MEMORY-таблицу не влезет? Или если это можно сделать покупкой, скажем, 16 гигов памяти?
Неактивен
1. Процесс еще долго висит в статусе "converting HEAP to MyISAM". Это тоже из-за нехватки памяти на таблицы?
А что говорит explain? Это может быть если временная таблица не влезла в память и на полпути должна быть перенесена на диск.
За стандартные решения еще то, что Вы сможете перейти на новые версии MySQL без потерь. При изменении исходников, Вам придется данные изменения поддерживать и дальше.
Неактивен
Explain говорит вот что:
1 SIMPLE w const PRIMARY PRIMARY const 1 Using index; Using temporary; Using filesort
1 SIMPLE i ref word_base_id word_base_id const 45597 Using where; Using index
Неактивен
попробуйте убрать из запроса таблицу w, написать where word_id in (N,M,K,..) и посмотреть explain
Неактивен
Попробовал. Если делать вот так:
select page_id, sum(weight) from search_index where word_id in (423944, 417700, 389452, 148886) group by page_id
Та же проблема с памятью, судя по всему.
Explain пишет:
1 SIMPLE search_index range word_base_id word_base_id NULL 571232 Using where; Using index; Using temporary; Using filesort
Отредактированно Shad (21.10.2007 02:55:23)
Неактивен
И все-равно остается проблема с "exclude words". Как удалить результаты, которые содержат связь с ненужными словами.
Можно это сделать в этом же запросе? Или все-равно только через промежуточную таблицу?
Неактивен
Можете прогонять "минус-слова" вторым деревом, а потом вычитать результаты.
Мне кажется, в текущей структуре это будет давать наиболее быстрые результаты.
Но структура, разумеется, не идеальная. Вам надо представить ее не в виде
таблицы, а в виде индекса - упорядоченные связи. Разумеется, не в рамках
обычного синтаксиса SQL.
Неактивен
Так, а где вычитать результаты? В промежуточной таблице? Или как-то можно иначе?
Что касается идеальности структуры - в таблице search_index как раз и есть индекс на все данные (word_id, page_id, weight)
Отредактированно Shad (21.10.2007 03:41:03)
Неактивен
Нет, Вы меня не поняли
Представьте себе данные абстрактно. Не в привязке к таблицам, а абстрактно. Просто как
элементы структурированного дерева. Вам нужно искать документы по словам. Значит, у
Вас есть некоторый набор слов. От этого набора есть ссылки на документы, в которых они
встречаются. У Вас есть запретные слова. Значит, у Вас есть ссылки от документов к словам.
Спуск по такому "двухуровневому" дереву будет, разумеется, куда быстрее, чем два раза
спуск по первому дереву. Но такое дерево надо писать. Вот тогда индекс будет оптимальным
Неактивен
Если Вам нужно сколько-то результатов (не не все), то возможно наличие стоп-слов выгоднее будет проверять только для найденных без учета стоп-слов документов. Это можно, в случае если наличие стоп-слов - исключает документ из выдачи, и можно с некой погрешностью, если стоп-слово понижает в выдаче.
Неактивен
Насколько я понимаю, вопрос не в стоп-словах, а в возможности запроса типа "+ель -зеленая"
Неактивен
если мы на "+ель" нашли 10000 документов, то легче взять первые 100 и их проверить на наличие слова "зеленая", таким образом мы быстрее найдем топовые документы, чем если 10000 доков со словом ель будем сравнивать с 10000 доков со словом зеленая
Неактивен
Ну, т.е. все-равно на практике через промежуточную таблицу?
Отредактированно Shad (21.10.2007 04:38:05)
Неактивен
