Предлагаю решить интересную 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%'
Комментарии
Во второй строчке дописываем t0.word LIKE 'Тест%' OR t0.word LIKE 'Test%' OR t0.word LIKE 'Слово%'
Найдутся скажем три id'шника, сджойнятся связывающие ряды, потом ряды fields, получится скажем с десяток рядов, потом группировка по объекту уберёт повторения у тех слов которые встречались дважды.
SELECT f.object_id
FROM word_fields wf0, word_fields wf1, fields f
WHERE f.id = ALL (ARRAY[wf0.field_id, wf1.field_id]) AND f.field = 'content'
AND (wf0.word_id, wf1.word_id) IN
(SELECT w0.id, w1.id FROM words w0, words w1 WHERE w0.word LIKE 'Test%' AND w1.word LIKE 'Тест%')
GROUP BY f.object_id
You have an error in your SQL syntax
В MySQL так f.id = wf0.field_id AND f.id = wf1.field_id