Что быстрее — INSTR или LIKE?

Полнотекстовый поиск абсолютно точно будет быстрее, как отметил кибибу в комментариях выше.

Однако:

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 только с подстановочным знаком суффикса намного быстрее.

MySQL — INSTR против LOCATE против LIKE против REGEXP

Для меня INSTR и LOCATE работали быстрее всего:

# 5.074 sec
SELECT BENCHMARK(100000000,INSTR('foobar','foo'));

# 5.086 sec
SELECT BENCHMARK(100000000,LOCATE('foo','foobar')); 

# 8.990 sec
SELECT BENCHMARK(100000000,'foobar' LIKE '%foo%');

# 14.433 sec
SELECT BENCHMARK(100000000,'foobar' REGEXP 'foo'); 

# 5.5.35-0ubuntu0.12.10.2 
SELECT @@version;

В случае «переднего подстановочного знака» (то есть предиката «LIKE '%...'»), как здесь, кажется, INSTR и LIKE должны работать примерно одинаково.

Когда подстановочный знак не является "передним подстановочным знаком", подход LIKE должен быть быстрее, если только подстановочный знак не является очень избирательным.

Причина, почему тип подстановочного знака и его селективность имеют значение, заключается в том, что предикат с INSTR() систематически приводит к сканированию таблицы (SQL не может делать никаких предположений о семантике подстановочных знаков). INSTR), в результате чего SQL может использовать свое понимание семантики предиката LIKE, чтобы, возможно, использовать индекс, чтобы помочь ему проверить только сокращенный набор возможных совпадений.

Как указано в комментарии к самому вопросу, полный текстовый индекс будет намного быстрее. Разница зависит от конкретного распределения слов в тексте, а также от общего размера таблицы и т. д., но ожидайте от двух до десяти раз более быстрого.

Возможным недостатком использования полнотекстового индекса, помимо общих накладных расходов на создание такого индекса, является то, что если кто-то не очень тщательно настроит этот индекс (например, определите список стоп-слов, используя специальный синтаксис поиска, чтобы избежать флективных форм и подобное...), могут быть случаи, когда результаты, предоставленные FullText, не будут такими, как ожидалось. Например, при поиске «SAW» (инструмент для резки дерева) можно получить множество совпадений с записями, включающими глагол «видеть» в его различных спряженных формах.
Конечно, эти лингвистически осведомленные функции полнотекстовых индексов обычно можно переопределить, а также можно считать, что такие функции являются фактически преимуществом, а не недостатком. Я просто упоминаю об этом здесь, поскольку мы сравниваем это с простым поиском по подстановочным знакам.

К тесту Раззеда добавить нечего. Но очевидно, что использование regexp требует гораздо большей вычислительной нагрузки, в отличие от того, на что указывает Сет в своем комментарии.

Следующие тесты предполагают, что вы установили query_caching в On в my.ini

query_cache_type = 1
query_cache_size = 64M

Тесты

• Тайминги показывают среднюю производительность из трех измерений (с периодической очисткой кеша):

• Нравится

  SELECT * FROM `domain_model_offers` WHERE `description` LIKE '%inform%' LIMIT 0 , 30
  

  Исходно: 0,0035 с
  Кэшировано: 0,0005 с

• РЕГЭКСП

  SELECT * FROM `domain_model_offers` WHERE `description` REGEXP 'inform' LIMIT 0 , 30
  

  Исходно: 0,01 с
  Кэшировано: 0,0004 с

Результат

LIKE или INSTR определенно быстрее, чем REGEXP.

Хотя разница во времени кэширования минимальна, она, вероятно, достаточна для дальнейшего исследования.

В вероятно сконфигурированной системе MySQL полнотекстовое индексирование обычно всегда должно быть быстрее или, по крайней мере, на одном уровне с неиндексированным поиском. Поэтому используйте индексирование, особенно для длинных текстов на человеческом языке, независимо от прерывистого кода разметки.