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

Если ваша цель состоит в том, чтобы проверить, существует ли строка в столбце MySQL (типа "varchar", "text", "blob" и т.д.), какая из следующих функций выполняется быстрее/эффективнее/лучше, и почему

Или, есть ли какой-нибудь другой метод, который подходит для любого из этих?

INSTR( columnname, 'mystring' ) > 0

против

columnname LIKE '%mystring%'

Ответ 1

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

Ответ 2

В случае "front wilcard" (т.е. предиката "LIKE" %... "), как представляется, здесь, INSTR и LIKE должны выполнять примерно то же самое.

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

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

Как указано в комментарии по самому вопросу, индекс Full Text будет намного быстрее. Разница зависит от конкретного распределения слов в тексте, а также от общего размера таблицы и т.д., Но ожидайте чего-нибудь от раза быстрее, чем в 10 раз быстрее.

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

Ответ 3

MySQL - INSTR vs LOCATE vs LIKE vs 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;

Ответ 4

Есть немного, чтобы добавить в razzed test. Но, по-видимому, использование 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.0035s
Кэш: 0.0005s

REGEXP

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

Начальная: 0,01 с
Кэш: 0.0004s

Результат

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

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

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