real - buscar php mysql criterios busqueda

Buscando una gran base de datos mysql con relevancia

Question

Estoy creando un motor de "búsqueda" bastante grande para la intranet de nuestra empresa, tiene entradas de 1miljon plus que se ejecutan en un servidor bastante rápido y, sin embargo, demora hasta 1 minuto para algunas consultas de búsqueda.

Así es como se ve la mesa.

Intenté crear un índice para él, pero parece que me falta algo, así es como se muestra el índice del programa.

y esta es la consulta en sí misma, es el ordenamiento que ralentiza la consulta en su mayoría, pero incluso una consulta sin ordenación es algo lenta.

SELECT SQL_CALC_FOUND_ROWS *
FROM `businessunit`
INNER JOIN `businessunit-postaddress` ON `businessunit`.`Id` = `businessunit-postaddress`.`BusinessUnit`
WHERE `businessunit`.`Name` LIKE ''tanto%''
ORDER BY `businessunit`.`Premium` DESC ,
CASE WHEN `businessunit`.`Name` = ''tanto''
THEN 0
WHEN `businessunit`.`Name` LIKE ''tanto %''
THEN 1
WHEN `businessunit`.`Name` LIKE ''tanto%''
THEN 2
ELSE 3
END , `businessunit`.`Name`
LIMIT 0 , 30

Cualquier ayuda es muy apreciada

Edición: lo que está ahogando esta consulta, el 99% está ordenando por relevancia con el carácter salvaje % Cuando hago una explicación, dice dónde; usando fsort

Answer 1

Creo que solo necesitas recolectar las claves, ordenarlas y luego unirte las últimas

SELECT A.*,B.* FROM
(
SELECT * FROM (
SELECT id BusinessUnit,Premium
CASE
WHEN Name = ''tanto'' THEN 0
WHEN Name LIKE ''tanto %'' THEN 1
WHEN Name LIKE ''tanto%'' THEN 2
ELSE 3
END SortOrder
FROM businessunit Name LIKE ''tanto%''
) AA ORDER BY Premium,SortOrder LIMIT 0,30
) A LEFT JOIN `businessunit-postaddress` B USING (BusinessUnit);

Esto todavía generará un filesort.

Es posible que desee considerar la precarga de las claves necesarias en una tabla separada que puede indexar.

CREATE TABLE BusinessKeys
(
id int not null auto_increment,
BusinessUnit int not null,
Premium int not null,
SortOrder int not null,
PRIMARY KEY (id),
KEY OrderIndex (Premuim,SortOrder,BusinessUnit)
);

Rellena todas las teclas que coincidan

INSERT INTO BusinessKeys (BusinessUnit,Premuim,SortOrder)
SELECT id,Premium
CASE
WHEN Name = ''tanto'' THEN 0
WHEN Name LIKE ''tanto %'' THEN 1
WHEN Name LIKE ''tanto%'' THEN 2
ELSE 3
END
FROM businessunit Name LIKE ''tanto%'';

Luego, para paginar, ejecute LIMIT solo en BusinessKeys

SELECT A.*,B.*
FROM
(
SELECT FROM BusinessKeys
ORDER BY Premium,SortOrder
LIMIT 0,30
) BK
LEFT JOIN businessunit A ON BK.BusinessUnit = A.id
LEFT JOIN `businessunit-postaddress` B ON A.BusinessUnit = B.BusinessUnit
;

CAVEAT : Utilizo LEFT JOIN lugar de INNER JOIN porque LEFT JOIN conserva el orden de las claves en el lado izquierdo de la consulta.

Answer 2

Debe probar la solución de búsqueda de esfinge que es un motor de búsqueda de texto completo que le dará un muy buen rendimiento junto con muchas opciones para establecer la relevancia.

Haga clic aquí para más detalles.

Answer 3

Es tan extraña la consulta :) Vamos a tratar de entender lo que hace.

Los resultados son menos de 30 filas de la tabla "businessunit" con algunas condiciones.

La primera condición es una clave externa de la tabla "businessunit-postaddress".
Verifique si tiene un índice en la columna businessunit-postaddress . BusinessUnit .

El segundo es un filtro para devolver filas solo con businessunit . Name comienza con ''tanto''.
Si no cometí un error, tiene un índice muy complejo. ¡''Business'' consta de 11 campos!
Y el campo ''Nombre'' no es el primer campo en este índice.
Por lo tanto, este índice es inútil cuando ejecuta la consulta "like tanto%".
Tengo fuertes dudas sobre la necesidad de este índice en absoluto.
Por cierto, exige recursos bastante grandes para mantener y ralentizar las operaciones de edición con esta tabla.
Tienes que hacer un índice con el único campo ''Nombre''.

Después de filtrar, la consulta clasifica los resultados y también lo hace de alguna manera extraña.
Al principio se ordena por campo de unidad de businessunit . Premium - es normal
Sin embargo, las siguientes afirmaciones con CASE también son inútiles.
Es por eso.
Los cero se asignan a Nombre = ''tanto'' (exactamente).
Las siguientes filas con una son filas con espacio después de ''tanto''; estas serán después de ''tanto'' en cualquier caso (excepto los símbolos especiales) porque el espacio es más bajo que cualquier letra.
Las siguientes filas con las dos son filas con algunas letras después de ''tanto'' (incluya espacio!). Estas filas estarán en este orden también por definición.
Y los tres están "reservados" para "otras" filas, pero no obtendrá "otras" filas. Recuerde acerca de [DONDE businessunit unidad de businessunit . Name LIKE condición ''tanto%''].
Así que esta parte de ORDER BY no tiene sentido.
Y al final de ORDER BY hay businessunit . Name nuevo ...

Mi consejo: necesita reconstruir la consulta desde cero teniendo en cuenta lo que desea obtener.

De todos modos supongo que puedes usar

SELECT SQL_CALC_FOUND_ROWS *
FROM `businessunit`
INNER JOIN `businessunit-postaddress` ON `businessunit`.`Id` = `businessunit-postaddress`.`BusinessUnit`
WHERE `businessunit`.`Name` LIKE ''tanto%''
ORDER BY `businessunit`.`Premium` DESC,
`businessunit`.`Name`
LIMIT 0 , 30

No se olvide de un índice en el campo businessunit-postaddress . BusinessUnit !

Y tengo una fuerte suposición sobre el campo Premium. Supongo que está diseñado para almacenar datos binarios (sí / no). Así que un índice ordinario (BTREE) no coincide. Tienes que usar el índice de mapa de bits.

PD: No estoy seguro de que realmente necesites usar SQL_CALC_FOUND_ROWS MySQL: Paginación - SQL_CALC_FOUND_ROWS vs COUNT () - Consulta

Answer 4

Es texto completo ( http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html ) o la coincidencia de patrones ( http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html ) desde php y mysql side.

De la experiencia y la teoría:

Ventajas del texto completo -
1) Los resultados son muy relevantes y la limitación de caracteres como el espaciado en la consulta de búsqueda no dificulta la búsqueda.
Desventajas del texto completo -
1) Hay palabras clave utilizadas como restricciones por los webhosters para evitar el exceso de carga de datos (por ejemplo, los resultados de búsqueda que contienen la palabra ''one'' o ''moz'' no se muestran. Y esto puede evitarse si está ejecutando su propio servidor manteniendo sin palabras de parada.
2) Si escribo ''ree'' solo muestra palabras que contienen exactamente ''ree'' no ''three'' o ''reed''.

Ventajas de la coincidencia de patrones -
1) No tiene palabras clave como en texto completo y si busca ''ree'', muestra cualquier palabra que contenga ''ree'' como ''reed'' o ''tres'' a diferencia de texto completo, donde solo se recupera la palabra exacta.
Desventajas de la coincidencia de patrones
1) Si se utilizan delimitadores como espacios en sus palabras de búsqueda y si estos espacios no aparecen en los resultados, ya que cada palabra está separada de cualquier delimitador, entonces no devuelve ningún resultado.

Answer 5

He leído la respuesta para usar Sphinx para optimizar la búsqueda. Pero en cuanto a mi experiencia aconsejaría una solución diferente. Usamos Sphinx durante algunos años y tuvimos algunos problemas desagradables con fallas de segmentación e índices corrompidos. Tal vez Sphinx no tenga tantos errores como hace unos años, pero desde hace un año estamos muy contentos con una solución diferente:

http://www.elasticsearch.org/

Los grandes beneficios:

• Escalabilidad: simplemente puede agregar otro servidor con una configuración casi nula. Si conoces la replicación de MySQL, te encantará esta característica.
• Velocidad: incluso con cargas pesadas, obtiene buenos resultados en mucho menos de un segundo
• Fácil de aprender: solo conociendo HTTP y JSON puede usarlo. Si eres un desarrollador web, te sientes como en casa.
• Fácil de instalar: se puede utilizar sin tocar la configuración. Solo necesita Java simple (sin Tomcat o lo que sea) y un Firewall para bloquear el acceso directo del público
• Buena integración de Javascript: incluso una herramienta similar a phpMyAdmin es una página HTML simple que usa Javascript: https://github.com/mobz/elasticsearch-head
• Buena integración de PHP con https://github.com/ruflin/Elastica
• Buen apoyo comunitario
• Buena documentación (no es agradable a la vista, pero cubre casi todas las funciones)

Si necesita una solución de almacenamiento adicional, puede combinar fácilmente el motor de búsqueda con http://couchdb.apache.org/

Answer 6

La mayoría de los sitios de búsqueda de motores de búsqueda son de búsqueda de texto FULL-TEXT-SEARCH . Será mucho más rápido en comparación con lo select y LIKE ... He agregado un ejemplo y algunos enlaces ... Creo que será útil para usted ... En esta búsqueda de texto completo también tienen algunas condiciones ...

PASO 1

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

PASO 2

INSERT INTO articles (title,body) VALUES
(''MySQL Tutorial'',''DBMS stands for DataBase ...''),
(''How To Use MySQL Well'',''After you went through a ...''),
(''Optimizing MySQL'',''In this tutorial we will show ...''),
(''1001 MySQL Tricks'',''1. Never run mysqld as root. 2. ...''),
(''MySQL vs. YourSQL'',''In the following database comparison ...''),
(''MySQL Security'',''When configured properly, MySQL ...'');

PASO 3
Búsquedas de texto completo en lenguaje natural:

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST (''database'');

Booleanas búsquedas de texto completo

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST (''+MySQL -YourSQL'' IN BOOLEAN MODE);

Ir a través de estos enlaces viralpatel.net , devzone.zend.com , sqlmag.com , colorado.edu , en.wikipedia.org

Answer 7

MySQL es bueno para almacenar datos, pero no es excelente cuando se trata de una búsqueda rápida basada en texto.

Además de Sphinx, que ya se ha sugerido, recomiendo dos motores de búsqueda fantásticos:

1. Solr con http://pecl.php.net/package/solr - motor de búsqueda muy popular. Utilizado en servicios masivos como NetFlix.

2. Elastic Search : software relativamente nuevo pero con una comunidad muy activa y mucho respeto

Ambas soluciones están basadas en la misma librería Apache Lucene.

Answer 8

Parece que el índice no cubre Premium , pero ese es el primer argumento ORDER BY .

Use EXPLAIN your query here para averiguar el plan de consulta y cambiar su índice para eliminar cualquier escaneo de tablas como se explica en http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Answer 9

Si "ORDER BY" es realmente el cuello de botella, la solución directa sería eliminar la lógica "ORDER BY" de su consulta y volver a implementar la clasificación directamente en el código de su aplicación utilizando la clasificación C #. Desafortunadamente, esto significa que también tendría que mover su paginación a su aplicación, ya que tendría que obtener el conjunto de resultados completo antes de poder clasificarlo y paginarlo. Solo menciono esto porque nadie más hasta ahora parece haberlo pensado.

Francamente (como han señalado otros), la consulta que mostró en la parte superior no debería necesitar indexación de texto completo. Un solo carácter comodín de sufijo (por ejemplo, LIKE ''ABC%'') debe ser muy efectivo siempre que haya un índice BTREE (y no un HASH) disponible en la columna en cuestión.

Y, personalmente, no tengo aversión ni siquiera al doble comodín (p. Ej., LIKE ''% ABC% "), que por supuesto nunca puede usar índices, siempre que el escaneo de una tabla completa sea barato. Probablemente, 250,000 filas es el punto. donde comenzaré a considerar seriamente la indexación de texto completo. 100,000 definitivamente no es un problema.

Sin embargo, siempre me aseguro de que mis SELECT sean lecturas sucias (no se aplica ninguna transaccionalidad a la selección).

¡Está sucio una vez que llega a los globos oculares del usuario en cualquier caso!

Answer 10

Si el argumento de LIKE no comienza con un carácter de comodín, como en su ejemplo, el operador LIKE debería poder aprovechar los índices .

En este caso, el operador LIKE debería tener un mejor desempeño que LOCATE o IZQUIERDA, por lo que sospecho que cambiar la condición de esta manera podría empeorar las cosas, pero aún creo que vale la pena intentarlo (¿quién sabe?):

WHERE LOCATE(''tanto'', `businessunit`.`Name`)=1

o:

WHERE LEFT(`businessunit`.`Name`,5)=''tanto''

También cambiaría su orden por cláusula:

ORDER BY
`businessunit`.`Premium` DESC ,
CASE WHEN `businessunit`.`Name` LIKE ''tanto %'' THEN 1
WHEN `businessunit`.`Name` = ''tanto'' THEN 0
ELSE 2 END,
`businessunit`.`Name`

El nombre debe ser LIKE ''tanto%'' ya, por lo que puede omitir una condición (CASE nunca devolverá el valor 3). Por supuesto, asegúrese de que el campo Premium esté indexado.

Espero que esto ayude.