Предлагаю решить интересную SQL-задачу. Думаю у среднего девелопера она займёт пол часа или больше (я же сразу спросил у SQL-гуру).

Возникла интересная задача при переходе с MyIsam на InnoDB. Как известно, полнотекстовый поиск у последнего движка отсутсвует, поэтому было два решения — делать обычное зеркало на MyIsam где был бы ктому же и очищенный от html текст (кстати так сделано в движке этого блога), либо более глубокая индексация с разбивкой на слова — я упоминал такую идею когда писал про морфологический поиск.

Второй вариант с одной стороны давал кучу головной боли с индексацией, очисткой, держанием многотысячной таблицы, логике поиска — задачи которые должна решать СУБД или расширенный индексатор типа sphinx. С другой стороны — выглядит инновативно, теоретически можно уже организовывать синонимы для тех или иных слов на уровне индекса или искать с учётом расстояния между словами. Вобщем гугл 2. На вскидку такой способ оказался в 6 раз быстрей InnoDB.

Сказано — сделано. Получилось 3 основных таблицы + собственно таблицы которые индексируются.

  • words — id, word (varchar, уникальный)
  • fields — id,  field (varchar, уникальный; значения например «title», «content»), object_id (int; указывает на id статьи),
  • word_fields — word_id, field_id, pos (int, положение в тексте)

Задача

Надо реализовать AND-поиск по этим таблицам с одним запросом где на входе будут два (или больше) слов, а на выходе — список уникальных object_id. Сложность в том, что связующая таблица word_fields имеет не уникальные пары word_id и field_id, поскольку в тексте статьи слова могут повторяться.

Для одного слова запрос выглядит так

SELECT t2.object_id
FROM (SELECT id FROM words t0 WHERE t0.word LIKE 'Тест%') as word_ids
INNER JOIN word_fields t1 ON (t1.word_id=word_ids.id)
INNER JOIN fields t2 ON (t2.id=t1.field_id AND t2.field='content')
GROUP BY object_id
LIMIT 10

Решение

Для OR:

SELECT f.object_id FROM search_fields f
INNER JOIN search_word_fields sf ON sf.field_id = f.field_id
INNER JOIN search_words w ON w.id = sf.word_id AND (w.word LIKE 'Test1%' OR w.word LIKE 'Test2%')

Для AND:

SELECT f.object_id FROM search_fields f
INNER JOIN search_word_fields sf1 ON sf1.field_id = f.field_id
INNER JOIN search_words w1 ON w1.id = sf1.word_id AND w1.word LIKE 'cat%'
INNER JOIN search_word_fields sf2 ON sf2.field_id = f.field_id
INNER JOIN search_words w2 ON w2.id = sf2.word_id AND w2.word LIKE 'dog%'


Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *