Как который быстрее — INSTR или?
Если Ваша цель состоит в том, чтобы протестировать, если строка существует в столбце MySQL (типа 'varchar', 'текста', 'блоба', и т.д.), какое из следующего является быстрее / более эффективный / лучше для использования, и почему?
Или, есть ли некоторый другой метод что вершины любой из них?
INSTR( columnname, 'mystring' ) > 0
по сравнению с
columnname LIKE '%mystring%'
2 ответа
FULLTEXT поиск будет абсолютно быстрее, как отметил Кибибу в комментариях выше.
Однако :
mysql> select COUNT(ID) FROM table WHERE INSTR(Name,'search') > 0;
+-----------+
| COUNT(ID) |
+-----------+
| 40735 |
+-----------+
1 row in set (5.54 sec)
mysql> select COUNT(ID) FROM table WHERE Name LIKE '%search%';
+-----------+
| COUNT(ID) |
+-----------+
| 40735 |
+-----------+
1 row in set (5.54 sec)
В моих тестах они работают точно так же. Оба они нечувствительны к регистру и обычно выполняют сканирование всей таблицы, что является недопустимым при работе с высокопроизводительным MySQL.
Если вы не выполняете поиск по префиксу в индексированном столбце:
mysql> select COUNT(ID) FROM table WHERE Name LIKE 'search%';
+-----------+
| COUNT(ID) |
+-----------+
| 7 |
+-----------+
1 row in set (3.88 sec)
В этом случае LIKE только с подстановочным знаком суффикса выполняется намного быстрее.
В случае "переднего подстановочного знака" (т.е. предиката "LIKE '%...'"), как кажется в данном случае, INSTR и LIKE должны работать примерно одинаково.
Когда подстановочный знак не является "передним подстановочным знаком", подход LIKE должен быть быстрее, если только подстановочный знак не очень избирателен.
Причина почему тип подстановочного знака и его избирательность имеют значение заключается в том, что предикат с INSTR() будет систематически приводить к сканированию таблицы (SQL не может делать никаких предположений о семантике INSTR), при этом SQL может использовать свое понимание семантики предиката LIKE, чтобы, возможно, использовать индекс для проверки только уменьшенного набора возможных совпадений.
Как указано в комментарии под самим вопросом, индекс полного текста будет намного быстрее. Разница зависит от конкретного распределения слов в тексте, а также от общего размера таблицы и т.д., но ожидайте от вдвое быстрее до 10 раз быстрее.
Возможным недостатком использования полнотекстового индекса, помимо общих накладных расходов на создание такого индекса, является то, что если не быть очень осторожным в настройке этого индекса (например: определение списка стоп-слов, использование специфического синтаксиса поиска, чтобы избежать инфинитивных форм и тому подобное...), могут быть случаи, когда результаты, предоставляемые FullText, будут не такими, как ожидалось. Например, при поиске "SAW" (инструмент для колки дров) можно получить много результатов по записям, включающим глагол "to see" в его различных спрягаемых формах.
Конечно, эти лингвистические особенности полнотекстовых индексов обычно можно отменить, и можно считать, что такие особенности являются преимуществом, а не недостатком. Я просто упоминаю об этом здесь, поскольку мы сравниваем это с обычным поиском по подстановочным знакам.