Поиск: FULLTEXT или LIKE?

    Наверное каждый разработчик подходил к моменту выбора между двумя решениями одной задачи и естесвенно решающим фактором выбора является наиболее быстрый способ (по времени процесса). Так и я в ходе разработки поиска для своей CMS задался вопросом: что лучше, делать поиск по базе используя полнотекстовой индекс или с помощью оператора сравнения LIKE, имея небольшое количество информации.

    Для того, чтобы ответить на свой вопрос я провел небольшой опыт: создал таблицу с четырмя полями (два из которых использовались для поиска и были проиндексированы FULLTEXT'ом) содержащую 5 000 строк. Поля по которым производился поиск содержали по 255 символов, случайно выбранных из одного большого текста. Поиск производился так же по случайным словам не короче 4-х символов.

    На первом этапе искалось одно слово в одном поле. На втором этапе в одном поле искалось одно из двух слов. На третьем этапе искалось одно из двух слов в обоих полях. На всех этапах поиск производился сначала с помощью конструкции MATCH(имя_поля) AGAINST('искомый_текст') потом с помощью LIKE.

    Примечание: тестировал на домашнем компьютере AMD 64 X2 4200, 2GB RAM, Apache, MySQL 5.0.

    Количество запросов на каждом этапе — 100. Частота — один раз в секунду.

    Итоги теста



    На графике показано среднее время выполнения поисковых запросов на 3-х этапах.



    Честно говоря, я ожидал немного другой заваисимости времени от сложности запроса и четкую границу между типами поисковых запросов. Но это объясняется тем, что поиск по FULLTEXT-индексу учитывает морфологию слова (при установленном дополнении и русской), что и создает дополнительную нагрузку.

    Преимущества и недостатки



    Преимущества поиска с помощью оператора LIKE:
    • незначительное увеличение времени обработки запроса при увеличении его сложности
    • возможность сортировки результатов
    • универсальность: можно использовать для поиска рпактически по любым типам полей, в отличии от полнотекстового


    Недостатки LIKE:
    • отсутствие поддержки морфологии
    • отсутствие модификаторов
    • поиск по всем строкам


    Преимущества полнотекстового поиска:
    • поддержка морфологии
    • выдача результатов по релевантности
    • наличие модификаторов, схожих с модификаторами в поске Гугла и Яндекса
    • стоп-слова
    • возможность настройки


    Недостатки:
    • отсутствие возможности сортировки
    • поддержка только VARCHAR и TEXT полей с индексами FULLTEXT
    • ресурсоемкий процесс
    • изначальная поддержка только MyISAM таблиц
    • при установленном ключе FULLTEXT добавление данных в таблицу происходи дольше


    Эпилог


    Эксперимент не претендует на идеальность, но, я считаю, он довольно наглядно показывает различия в скорости между двумя видами поиска, при небольшом размере базы данных. По-моему, мнению оба варианта имеют право на существование, однако в разных ситуациях.

    UPD: Под разными ситуациями подразумеваются случаи с разным количеством строк в таблице. Оператор LIKE лучше когда записей не огромное количество и он не применим в таблицах с сотнями тысяч строк, по причине того, что поиск, производится по всем записям.

    P.S.: прочитать про полнотекстовой поиск можно здесь.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 67

      0
      1. уберите автооформление и сделайте

      2. ...ответственность...
      3. Преимущества.

      зы плюс в карму
        0
        сделайте оформление с помощью, например, тэгов P и BR, а то сейчас много пустых строк.
          0
          готово
        0
        спасибо. полезная вещ. +1 в карму.
          0
          Присоеденяюсь. Сам постоянно об этом задумываюсь, да времени нет самому разобраться.
            0
            Слово «вещь» пишется вот-так.
              +1
              Grammar Nazi: без дефиса.
            +10
            Ждем аналогичного теста, но с 50 000, 50 0000 и 5 000 000 строк. Тогда будет что обсуждать и будут основания для выводов.
              0
              Спасибо! У меня один вопрос, можеть быть и не в тему. Но.. Если в базе имеется вот такие данные.
              <font color="#fff"> Blablabla</font>
              Как будет выглядеть запрос, что бы не искать html теги? Тут у меня в примере только , но можеть быть и другие html теги. Не подскажете как избежать проблему? А то когда искать слово font, запрос вернет мне все поля где есть html тег font.
                0
                Тут у меня в примере только <font>
                  +1
                  Нужно завести отдельное поле, в котором будут объединены все поля, по которым нужно осуществлять поиск. В этом поле должен храниться текст, очищенный от html.
                    0
                    Как тут можно теги отдельно сохранить не пойму. Кажеться вы меня не так поняли. Вот например есть cms, пишу новость, html теги разрешены. Добавляем новость:

                    <ul>
                    <li><a href="#">Lorem ipsum dolor sit amet, consectetuer adipiscing elit.</a></li>
                    <li><a href="#">Fusce bibendum ultricies lorem.</a></li>
                    <li><a href="#">Nulla facilisis odio vitae neque.</a></li>

                    <li><a href="#">Etiam sit amet purus a eros viverra adipiscing.</a></li>
                    <li><a href="#">Nunc dignissim eleifend turpis</a></li>
                    </ul>
                    <br class="spacer" />
                    </div>


                    А тепер в поиске напишем слово spacer.А тепер? Вот что я имел ввиду.
                      +1
                      Заведите в базе ещё одно поле, где будет храниться чистый текст, без тегов.
                        0
                        А понятно. Но значить база в два раза увеличиться тогда. Жаль что нет способа попроще.
                          0
                          размер довольно дешев, так что можно пожертвовать винтом или плашкой озу ради время поиска, имхо.
                    0
                    Если наименование различных тегов ограниченно, то советую использовать Stopwords list
                    (http://dev.mysql.com/doc/refman/5.0/en/f…)
                      0
                      Есть в php функция htmlspetialchars.
                        0
                        Ну и? Пусть в базе данные будет в таком виде.
                        <font> Blabla </font>
                        И что это меняет?Всё ровно во время поиска слово font запрос увидеть html тег- font.
                          0
                          елки палки! Хабр меняет код! забей!
                      0
                      в fulltext есть сортировка по релевантности
                        0
                        прошу прощения, неверно прочитал.
                        +1
                        На сколько я помню, fulltext имел проблемы с русской морфологией. Давно уже не использовал его, надо будет как-то проверить, изменилась ли ситуация.

                        Стоп-слова можно и в like использовать. (`parent` like 'мама' and `parent` not like 'папа')

                        Есть ещё и regexp, с которым возможны такие конструкции: `parent` regexp 'мам(а|у|е|ы|ой)'. Его было бы интереснее сравнить с `parent` like '%мама%' and `parent` like '%маму%' and `parent` like '%маме%' and `parent` like '%мамы%' and `parent` like '%мамой%'.
                          +3
                          LIKE '%чтототам' вообще зло. Оно индекс не использует.
                            0
                            Что не использует, это понятно. Но в большинстве случаев нужно искать слово посреди текста, а не в его начале. Поэтому и тесты нужно проводить соответственные. А то ведь можно и «=» для поиска нужной строки использовать. ;)
                            +1
                            Года 2 назад писал подобный поиск, со словарем. Работает нереально долго, но иногда просто незаменим.
                              0
                              Пожалуй насчет "нереально долго" я соврал, все-же за приемлемое время. Например запрос по 3-м словам, в нескольких таблицах(допустим 5000 записей), в нескольких полях(2-3) занимал не более 5 секунд, на хорошем хостинге. Надо будет поковырять на досуге.
                              Использовался словарь - примерно 800 000 словоформ, с 2-мя полями "слово/словоформа" для выборки словоформ. Эксперименты по его оптимизации не проводил, хотя там можно все сделать программно из гораздо меньшей базы. Просто по сравнению с самим запросом, выборка из словаря занимает исчезающе малое время.
                            0
                            "поиск по словам не короче 4-х символов"
                            Ну это же настраивается. Можно и два символа, только индекс будет больше и тормозить будет сильнее.
                              0
                              Спасибо. Пожалуй удалю этот пункт.
                              +3
                              Автор, вы осознаете, что LIKE '%...%' - это всегда full scan всей таблицы, а FULLTEXT - нет?
                              В таблице из миллиона строк о варианте с LIKE речи идти не может, ну а тестировать производительность на таблице в 5000 строк бессмысленно.

                              PS. Я сам никогда не имел дела с FULLTEXT, если что :)
                              PPS. "Apache (без дополнительных модулей)" - боюсь даже предположить, почему это релевантно топику.
                                0
                                Спасибо за мнение, согласен. Но не на всех сайтах и 1000 строк есть, мое упущение, что я не сказал об этом.
                                  0
                                  А при 1000 записей об полнотекстовом поиске и не думают.
                                +2
                                В свое время пришлось реализовать поиск по таблице в которой более 7000000 строк. LIKE отрабатывал по ней около 20 минут, в то время как FULLTEXT (IN BOOLEAN MODE) - менее секунды.
                                  +2
                                  Согласен. Fulltext хорош на больших текстах, не на полях в 250 символов.
                                  И в больших текстах он рвет Like как тузик грелку.
                                  Хотя до полноценного поиска на больших размерах не дотягивает.

                                  Советую тем, кому нужен FullTextSearch смотреть в сторону последнего PostgresQL,
                                  в нем модуль ранее известный как tsearch2 теперь часть ядра, всторенный стемминг русского, английского, можно прикрутить словари и т.п.
                                  Специальные индексы GIN и GiST для FullTextSeach, возможность задавать слова с разным весом для заголовков и других частей текста...
                                  На порядок мощнее.
                                    0
                                    У меня таблица с 9.000.000 строк. Колонки id(int), title(varchar)

                                    Запрос (на VDS) типа:

                                    SELECT * FROM `titles` WHERE
                                    title title like '%string1%'
                                    AND title like '%string2%'
                                    AND title like '%string3%'
                                    AND title like '%string4%'
                                    LIMIT 50

                                    4.5800 сек.

                                    В данном случае мне FULLTEXT не подходит так как морфология не важна, а нужна 100% гарантия вхождения искомых слов и 100% результат даже при очень большом результате поиска.
                                      0
                                      Попробуйте использовать FULLTEXT IN BOOLEAN MODE и свои проверки тоже. Сначала ищет записи по индексу, потом фильтрует найденное лайком
                                    +1
                                    Ваш самый быстрый запрос был выполнен за 0,019 сек, сымый долгий за 0,159. По сути - никакой разницы абсолютно. Но сам пост интересен, пишите еще, уважаемый автор!
                                      –1
                                      В рамках целой CMS этот разрыв очень большой и значительный, при том, что генерация только самой страницы за 0.1, на мой взгляд это уже непозволительно много.
                                      0
                                      А можно конкретней про условия испытания? Как запускали, какая операционная система?
                                        0
                                        Windows Vista, Отдельные скрипты PHP (6 штук) для каждого варианта, высчитывал среднее значение тоже естественно скриптом, причем значения до тысячных не менялись при создании новой БД. Повторюсь, что эксперимент не претендует на чистоту, т.к. не сервер, тем более не виртуальный. Условия так сказать домашние :)
                                          –1
                                          Я тебе могу сказать, что эксперимент не то, что на чистоту - вообще не может на что-либо претендовать... Начать хотя бы с того, что замерять скорость работы MySQL через PHP... Хотя бы через консольное приложение MySQL'я запустил...
                                            0
                                            пользователи тоже будут запускать поиск через консоль?
                                              0
                                              А какое отношение PHP имеет к "FULLTEXT или LIKE"?
                                        –2
                                        Решал проблему FULLTEXT-ом, когда зашёл в тупик изобретая велосипед умного поиска :)
                                        Больше всего в нем порадовало это приоритеты.
                                        По скорости хочется верить, что таки обгоняет LIKE '%...%', но сам не экспериментировал не знаю.
                                          0
                                          Сканировал таблицу, вырезал html, приводил все слова в именительный падеж, удалял "обычный язык" - операторы "обычной речи"
                                          Потом результат в другую таблицу( так как на главную MyISAM вешать не охота) и .. просто радоваться :)
                                          Лучше фултекста только создание кишкообразных таблиц вида docuID,wordID ...
                                          Которые по сути дублируют внутрений функционал фултекста, но поддаются какому угодно контролю...
                                            0
                                            а как слова приводили в именительный падеж?
                                              0
                                              1.str_wordcount
                                              2.создаем таблицу слов( 600к слов)
                                              3.выбираем слово, пробуем отрезать и добавлять окончания и ищем это в базе
                                              таким вот методом тыка понимать что это(глагол, прилагательное, существительное)
                                              После чего наличие некоторых окончаний определяет форму слова( забыл как по научному называется, вроде как склонение)
                                              расставляет флаги у слов - какое,что,в каком падеже..
                                              можно использовать.
                                              База такая генериться день :)
                                              Делал для "гиперконтекста"
                                              Бонус - система самособираемая. Даже "падонкофский" язык может просклонять если найдет записи с правильными окончаниями( аффтар, аффтара, аффтару... )
                                            +2
                                            В образовательных целях:
                                            Для повышения грамотности: «и естественно решающим», «четырьмя», «заваисимости», «рпактически по любым типам полей», «поиске Гугла» и т.д. и т.п.
                                              0
                                              Ну, мы в данном случае, наверно, говорим о возможностях поиска самой БД.
                                              Хотя в образовательных целях подойдет. :)
                                                0
                                                Чего только не напишешь при температуре 39... спасибо
                                                +2
                                                Бенчить MySQL надо с использованием select benchmark(...), а не php-скриптами, и уж точно не из браузера под апачем, если что.
                                                И full-text index, и like неприменимы на больших объемах данных и важных задачах. Правильный метод - использование внешних поисковых движков типа сфинкса, ну и like для мелких фенечек, которые возникают три раза в год :)
                                                  0
                                                  Стоило бы подчеркнуть, что это всё только в контексте MySQL. У других СУБД с full-text index всё значительно лучше и нет необходимости во внешних движках, с которыми сложность системы возрастает и теряется возможность проведения комплексных SELECT-ов (полнотекстовый фильтр + доп. ограничения по интервалам и т.п.)
                                                    0
                                                    P.S.: этот коммент — реакция на слова "правильный метод", но ни в коем случае не попытка развести какой-либо спор.
                                                      0
                                                      Хорошее уточнение :)
                                                      Хотя насчет сложности использования внешних движков очень хочется поспорить, особенно учитывая сфинкс как pluggable storage engine к MySQL.
                                                  +1
                                                  1. нет запросов
                                                  2. эти запросы никогда не будут эквивалентны по выдаче, как их можно сравнивать?
                                                    0
                                                    >> поддержка только VARCHAR и TEXT полей с индексами FULLTEXT
                                                    боюсь представить, по каким ещё типам полей можно осуществлять полнотекстовый поиск? :-)
                                                    0
                                                    мне представляется сравнение двух настолько разных инструментов, как like и match() через fulltext, мягко говоря, странным
                                                    функционально fulltext намного богаче, и часто вы сможете like'ми заменить его только написав гору кода.
                                                    даже без подключенной русской морфологии прекрасно работает убирание окончаний стеммером Портера в реализации Котерова (найдите яндексом) и замена окончаний на *

                                                    fulltext за счет языка запросов, сортировщика по релевантности должен использоваться для решения других задач и на другом уровне.

                                                    кроме того, не забудьте о том, что fulltext indexes тормозят вставки. на больших таблицах это немаловажно

                                                    если уж вы пишете такой тест, то не стоит сравнивать тупо запуск двух серий запроса, лучше попробовать грамотно сравнить реализацию, скажем, по форуму или хабру поиска через эти дву пути (и то не представляю, как вы like'ом полученную выдачу будете по релевантности сортировать)

                                                    на реальных задачах, которые я решал при написании программного макета к диссертации fulltext давал очень хорошее быстродействие на базах в сотни тысяч больших статей, а вот lile %s% на словаре в 100 тысяч слов задумывался очень крепко.

                                                    сравнение познавательное, но, простите, слабенькое.
                                                    плюсанул вас два раза, пишите еще.
                                                      –2
                                                      Мускуле фултекст убогий, используйте сфинкс.
                                                      • НЛО прилетело и опубликовало эту надпись здесь
                                                          0
                                                          а что не говно?
                                                          0
                                                          (убогий по сравнению с постгресовским tsearch) -2 минуса и не одного комментария! Ну же! Отписываемся почему минусуем!
                                                          0
                                                          Исследование интересное, спасибо и плюс вам в карму.
                                                          Но, имхо, всё-таки не совсем корректно подобным образом сравнивать LIKE и полнотекстовый поиск, т.к. сферы применения всё-таки несколько разные.
                                                            0
                                                            Было бы интересно видеть аналогичный сравнительный тест для Postgres.
                                                              0
                                                              6000000 записей ilike 3 секунды sphinx 0.01
                                                            • НЛО прилетело и опубликовало эту надпись здесь
                                                                0
                                                                Мне понравилось. Учитывая, что я фултекст не юзал никогда - автору плюс за просвещение =)
                                                                  0
                                                                  Присоединюсь к большинству, 5 000 записей — и правда маловато.
                                                                  И запросов нет (
                                                                  По поводу замеры с помощью PHP тоже двояко, с одной стороны результаты получаются не столь «чистые» как при использовании MySQL Console, но с другой пользователи действительно будут получать результаты через сайт, а не через консоль.
                                                                  Для чистоты я бы предложил записей сделать побольше на 2-а порядка и результаты, и при помощи консоли, и при помощи PHP.

                                                                  P.S. 100 запросов раз в секунду — слабая нагрузка, вопросы связанные с «прелестями» поиска появляются имхо на более высоконагруженных, так что я бы выполнял 1 000 (или даже 10 000) с плотностью — 100 / сек.
                                                                    0
                                                                    Добрый день, уточняющий вопрос
                                                                    два из которых использовались для поиска и были проиндексированы FULLTEXT'ом

                                                                    Какие именно индексы вы использовали?
                                                                    По задумке теста ожидается использование:
                                                                    • индекс по первой колонке
                                                                    • индекс по (первой + второй) колонкам


                                                                    Но в статье на нашел описание выбранных индексов и могу предположить, что использовались индексы отдельно второй и третей колонок, а не совместный индекс. Если это так — то это может являться причиной, по которой третий тест дал плохой результат.

                                                                    Добавьте, пожалуйста, список индексов, спасибо!

                                                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                    Самое читаемое