За последние 24 часа нас посетили 18083 программиста и 1652 робота. Сейчас ищут 1594 программиста ...

Поиск ключевых слов в таблице

Тема в разделе "Решения, алгоритмы", создана пользователем DZEN, 21 авг 2012.

  1. DZEN

    DZEN Активный пользователь

    С нами с:
    10 сен 2007
    Сообщения:
    250
    Симпатии:
    0
    Задача весьма не уникальная, но у меня даже приблизительно не может сформироваться в голове правильная реализация поиска. Необходимо производить поиск в тексте сообщения на наличие ключевых слов и добавлять их в таблицу. То есть ищем в message.text предложения из message_key.word и при совпадении пишем в таблицу message_to_key. Мини проблема - структуру таблиц менять не желательно, так как доступ к ним я корее всего не получу.

    Таблицы:
    Код (Text):
    1. CREATE TABLE IF NOT EXISTS `message` (
    2.   `id` int(10) NOT NULL AUTO_INCREMENT,
    3.   `title` varchar(255) NOT NULL DEFAULT '',
    4.   `text` text NOT NULL,
    5.   PRIMARY KEY (`id`),
    6.   KEY `title` (`title`)
    7. );
    Код (Text):
    1. CREATE TABLE IF NOT EXISTS `message_key` (
    2.   `id` int(10) NOT NULL AUTO_INCREMENT,
    3.   `word` varchar(255) NOT NULL DEFAULT '',
    4.   PRIMARY KEY (`id`),
    5.   FULLTEXT KEY `word` (`word`)
    6. );
    Код (Text):
    1. CREATE TABLE IF NOT EXISTS `message_to_key` (
    2.   `id` int(10) NOT NULL AUTO_INCREMENT,
    3.   `message_id` int(10) NOT NULL DEFAULT '0',
    4.   `key_id` int(10) NOT NULL DEFAULT '0',
    5.   PRIMARY KEY (`id`),
    6.   KEY `key_id` (`key_id`),
    7.   KEY `message_id` (`message_id`)
    8. );
     
  2. sobachnik

    sobachnik Старожил

    С нами с:
    20 апр 2007
    Сообщения:
    3.380
    Симпатии:
    13
    Адрес:
    Дмитров, МО
    Этот скрипт нужно будет запустить один раз, чтобы он перебрал всю таблицу единожды - и всё? Или нужно будет делать это регулярно? Просто если база большая, то очень ресурсоёмкая задача получается - долго будет обрабатываться... Это похоже на перестроение поискового индекса для форумов на движке phpbb :)
     
  3. DZEN

    DZEN Активный пользователь

    С нами с:
    10 сен 2007
    Сообщения:
    250
    Симпатии:
    0
    3000 записей в message_key без последующего увеличения, поиск запускается один раз при добавлении записи в message, время где-то каждые 15-30 мин.
     
  4. sobachnik

    sobachnik Старожил

    С нами с:
    20 апр 2007
    Сообщения:
    3.380
    Симпатии:
    13
    Адрес:
    Дмитров, МО
    Тогда, наверно, так:
    При первом запуске - получаешь список всех ключевых слов из message_key:
    Код (Text):
    1. SELECT * FROM `message_key`
    Перебираешь в цикле и для каждого слова - получаешь id сообщений, в которых оно присутствует:
    Код (Text):
    1. SELECT `id` FROM `message` WHERE `text` REGEXP '[[:<:]]$word[[:>:]]';
    И, соответственно, вставляешь в базу найденные соответствия между id из первого запроса (из message_key) и второго (message).

    При последующих запусках - в принципе всё также, но искать соответствия нужно будет уже не по всей таблице, а только в новой добавленной записи (т.е. ещё одно условие для WHERE):
    Код (Text):
    1. SELECT `id` FROM `message` WHERE `id` > id_последней проверенной записи AND `text` REGEXP '[[:<:]]$word[[:>:]]';
    Добавлено спустя 28 минут 7 секунд:
    Ещё вариант, который избавит от огромного количества запросов к б.д. и, возможно, ускорит работу скрипта - можно загрузить все сообщения из таблицы message в PHP (массив создать), если памяти хватит конечно, и уже искать с помощью регулярок preg_match(). Это всё равно будет долго, конечно, но скорее всего, что быстрее, чем если делать запрос к б.д. на каждое ключевое слово. Хотя это можно и потестить - я не исключяю (просто не в курсе), что регулярки в MySQL окажутся намного быстрее, чем PHP-шные и тогда вариант 3000 запросов может оказаться и быстрее.
     
  5. DZEN

    DZEN Активный пользователь

    С нами с:
    10 сен 2007
    Сообщения:
    250
    Симпатии:
    0
    Смотря как строить. Да, в данном лучае PHP быстрее чем MySQL FULLTEXT. Только как сделать кеширование MySQL запроса для меток? Я таким ниразу не занимался.
    Код (Text):
    1.  
    2. <?php
    3. # получаем массив меток в виде ['нужная метка'=> 111, 'ещё метка'=> 222]
    4. $tag = db::select_keyval('SELECT `id`, `word` FROM `message_key` ORDER BY `word` ASC', 'word', 'id');
    5. # заменяем id метки на "{id}", по типу шаблонизатора (в тексте не будет строк "{123}" ?!?!)
    6. $tag = array_map(function($id){return '{'.$id.'}';}, $tag);
    7. # меняем: "нужная метка и нужная метка да ещё метка" на "{111} и {111} да {222}"
    8. $text = strtr($news['text'], $tag);
    9. $matches = [];
    10. $res = preg_match_all('/{(.*?)}/', $text, $matches, PREG_PATTERN_ORDER); # получаем массив первой подмаски
    11. if ($res < 1) return print 'подходящие метки не не найдены';
    12. # сейчас массив в виде [0=> 111, 1=> 111, 2=> 222], убираем повторяющиеся id меток
    13. $matches = array_unique($matches[1], SORT_NUMERIC);
    14. # сейчас массив в виде [0=> 111, 2=> 222], получаем только нужные метки
    15. $tag = db::select_keyval('SELECT `id`, `word` FROM `message_key` WHERE `id` IN ('.
    16.     implode(', ', $matches).') ORDER BY `word` ASC', 'id', 'word');
    17. # дальше на своё усмотрение