not - mysql like wildcards

¿Qué es más rápido, INSTR o LIKE?

Question

Si su objetivo es probar si existe una cadena en una columna MySQL (de tipo ''varchar'', ''text'', ''blob'', etc.) ¿cuál de las siguientes opciones es más rápida / más eficiente / mejor de usar, y por qué?

O bien, ¿hay algún otro método que encabece alguno de estos?

INSTR( columnname, ''mystring'' ) > 0

vs

columnname LIKE ''%mystring%''

Answer 1

En el caso de una "tarjeta wil frontal" (es decir, un predicado "LIKE ''% ...''" como parece ser el caso aquí, INSTR y LIKE deben realizar más o menos lo mismo .

Cuando el comodín no es un "comodín frontal", el enfoque LIKE debería ser más rápido, a menos que el comodín no sea muy selectivo.

El motivo por el que importa el tipo de comodín y su selectividad es que un predicado con INSTR () resultará sistemáticamente en un escaneo de tabla (SQL no puede hacer suposiciones sobre la semántica de INSTR), por lo que SQL puede aprovechar su comprensión de la semántica del LIKE predicate quizás usar un índice para ayudarlo a probar solo un conjunto reducido de posibles coincidencias.

Como se sugiere en el comentario bajo la pregunta en sí, un índice de texto completo será mucho más rápido . La diferencia depende de la distribución específica de las palabras dentro del texto, y también del tamaño de la tabla general, etc., pero se puede esperar algo desde el doble de rápido hasta tal vez 10 veces más rápido.

Una posible desventaja de usar en el índice de texto completo, además de la sobrecarga general para crear dicho índice, es que a menos que se tenga mucho cuidado al configurar este índice (por ejemplo, definir la lista de palabras de detención, usar una sintaxis de búsqueda específica para evitar similares ...), puede haber casos en que los resultados proporcionados por FullText no sean los esperados . Por ejemplo, al buscar una "SIERRA" (una herramienta para cortar madera), se pueden obtener muchos éxitos para los registros, incluido el verbo "ver", en sus diversas formas conjugadas.
Por supuesto, estas características de los índices de texto completo que tienen en cuenta la lingüística generalmente se pueden anular y también se puede considerar que tales características son efectivamente una ventaja, no un inconveniente. Solo menciono esto aquí ya que estamos comparando esto con una búsqueda de comodín simple.

Answer 2

Hay poco que agregar a la prueba de Razzed. Pero aparentemente el uso de regexp genera una carga de procesamiento mucho más pesada , a diferencia de lo que señala Seth en su comentario.

Las siguientes pruebas asumen que configuró query_caching en On en mi.ini

query_cache_type = 1
query_cache_size = 64M

Pruebas

• Los tiempos muestran el rendimiento promedio, de tres mediciones (con el caché borrado intermitentemente):

• ME GUSTA

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

  Inicial: 0.0035s
  En caché: 0.0005s

• REGEXP

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

  Inicial: 0.01s
  En caché: 0.0004s

Resultado

LIKE o INSTR es definitivamente más rápido que REGEXP .

Aunque mínima, la diferencia de tiempo de caché es probablemente suficiente para justificar una investigación adicional.

En un sistema MySQL probablemente configurado, la indexación de texto completo generalmente debería ser siempre más rápida o al menos a la par con una búsqueda no indexada. Por lo tanto, utilice la indexación, especialmente en textos largos en lenguaje humano, independientemente del código de marcado intermitente.

Answer 3

Las búsquedas de FULLTEXT van a ser más rápidas, como señaló kibibu en los comentarios anteriores.

Sin embargo :

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)

En mis pruebas, realizan exactamente lo mismo. Ambos distinguen entre mayúsculas y minúsculas y, en general, realizan escaneos de tabla completa, un no-no general cuando se trata de MySQL de alto rendimiento.

A menos que esté haciendo una búsqueda de prefijo en una columna indexada:

mysql> select COUNT(ID) FROM table WHERE Name LIKE ''search%'';
+-----------+
| COUNT(ID) |
+-----------+
| 7 |
+-----------+
1 row in set (3.88 sec)

En cuyo caso, el LIKE con solo un comodín de sufijo es mucho más rápido.

Answer 4

MySQL - INSTR vs LOCATE vs LIKE vs REGEXP

Para mí, el INSTR y el LOCATE realizaron el más rápido:

# 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;