Предлагаю решить интересную 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%'