mysql - tutorial - El mejor tipo de indexación cuando hay cláusula LIKE

todo sobre elasticsearch

Question

Esta pregunta ya tiene una respuesta aquí:

• mejorar el rendimiento para LIKE cláusula 3 respuestas

Aquí está mi consulta:

SELECT name, usage_guidance, total_used_num
FROM tags
WHERE
 ( name LIKE CONCAT(''%'', ?, ''%'') OR
   usage_guidance LIKE CONCAT(?, ''%'') )
 AND name NOT IN ($in)
ORDER BY name LIKE CONCAT(''%'', ?, ''%'') DESC, name ASC
LIMIT 6

¿Cuál es el mejor índice?

• tags(name,usage_guidance)
• tags(usage_guidance,name)
• tags(name)
• tags(usage_guidance)

¿O hay alguna mejor opción? Ya sabes, cuando aparece LIKE , me estoy confundiendo sobre la creación de índices. Porque LIKE %something nunca tomaría ningún beneficio de los índices. También en la consulta anterior, tengo tanto AND , OR como IN . Es por eso que hice esta pregunta para conocer tu opinión al respecto también.

Aquí está la estructura de mi mesa:

CREATE TABLE `tags` (
    `id` int(11) NOT NULL,
    `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `usage_guidance` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `parent_id` int(11) UNSIGNED DEFAULT NULL,
    `related` int(11) UNSIGNED DEFAULT NULL,
    `total_used_num` int(11) UNSIGNED NOT NULL,
    `date_time` int(11) UNSIGNED NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

Y estoy tratando de hacer una consulta de sugerencia de autocompletar. Algo como esto:

Answer 1

Sí, lo que tienes aquí es un asesino de base de datos

Un índice de árbol B se puede usar para comparaciones de columnas en expresiones que usan los operadores =,>,> =, <, <= o BETWEEN. El índice también se puede usar para las comparaciones LIKE si el argumento para LIKE es una cadena constante que no comienza con un carácter comodín.

Fuente: http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

Entonces eso significa que su consulta LIKE no puede usar el índice y luego tiene dos me gusta conectados con un OR. Si eso no es suficiente, también has lanzado una comparación NO EN.

Pero afortunadamente, la segunda expresión LIKE no es tan mala, no comienza con un comodín. Entonces, su mejor esperanza es crear un índice compuesto en usage_guidance, name

Si pudieras publicar tu SHOW CREATE TABLE y unas pocas líneas de datos de muestra + el resultado esperado, podríamos hacerte una idea si hay una forma de reescribir esta consulta.