[SOLUCIONADO] consulta fulltext bloquea el servidor

[evoarte]

buenas tardes,

no he utilizado hasta el momento mysql e intento esta consulta que bloquea el servidor:

Código MySQL:

SELECT c.clave, c.codigo, c.unidad, c.resumen, c.precio, c.fecha, c.tipo, t.texto, 
MATCH (
t.texto
)
AGAINST (
 'texto1 texto2 texto3'
) AS score
FROM tablac AS c
INNER JOIN tablat AS t ON c.codigo = t.codigo
WHERE MATCH (
t.texto
)
AGAINST (
 'texto1 texto2 texto3'
)
AND c.codigo LIKE'D_______'
AND c.codigo NOT LIKE'D50_____'
ORDER BY c.codigo, score
LIMIT 40
 

[evoarte]

buenas tardes,

trato de averiguar si es posible optimizar la consulta, por que tarda casi 1 minuto en ejecutarse.

gracias.

un saludo,
josé carlos.

[gnzsoloyo]

Yo empezaría probando eliminar momentáneamente el ORDER BY, para verificar si eso tiene mal impacto o no. Los ORDER BY y GROUP BY son asesinos de performance, y si se peuden descartar, mejor.
Por otro lado, los LIKE que usas no tienen sentido si no los vas a usar con comodines. Tal como los usas, ponerlo así´:

Código MySQL:

...
AND c.codigo LIKE'D_______'
 

Es igual que ponerlo así:

Código MySQL:

...
AND c.codigo = 'D_______'
 

Además, si ese fuese el caso, el NOT LIKE es totalmente innecesario, porque estás operando sobre el mismo campo, por lo que la primera condición descarta la segunda. Es una comparación innecesaria.
Pruébalo así y dinos cuánto tiempo tarda de este modo:

Código MySQL:

SELECT c.clave, c.codigo, c.unidad, c.resumen, c.precio, c.fecha, c.tipo, t.texto, 
MATCH (t.texto) AGAINST ('texto1 texto2 texto3') score
FROM tablac AS c
  INNER JOIN tablat AS t ON c.codigo = t.codigo
WHERE 
  MATCH (t.texto) AGAINST ('texto1 texto2 texto3')
AND c.codigo = 'D_______'
LIMIT 40
 

[evoarte]

hola, gracias por responder

Código MySQL:

AND c.codigo LIKE 'D_______'
AND c.codigo NOT LIKE 'D50_____'
 

estas condiciones LIKE son para que la consulta devuelva solo los registros que:
1. comiencen por D y tengan 7 caracteres más, utilizando el carácter comodín "_"
2. y que no comiencen por D59 y tengan 5 caracteres más.

Código MySQL:

ORDER BY score DESC
 

necesito ordenar por la relevancia de la búsqueda, de mayor a menor.


todos los elementos del select lo veo necesarios pero no sabría si se puede optimizar o es cosa del rendimiento del servidor.

un saludo,
josé carlos.

[gnzsoloyo]

Estamos primero viendo el problema de la performance. Por eso te pido que pruebes sin el ORDER BY.
La idea es verificar si el impacto es grande. SI no hay diferencia de performance significativa, significaría que el problema son las condiciones de la consulta, y no el ordenamiento.

Lo que pretendo que hagas es hacer un análisis exhaustivo y detallado de las causas de la baja performance. En este punto no me importa cuál es el ordenamiento final, eso se resuelve después.

[evoarte]

bien, procedemos, la consulta inicial ejecutada es esta y el resultado:

Mostrando registros 0 - 39 (40 total, La consulta tardó 56.9137 seg):

Código MySQL:

SELECT c.clave, c.codigo, c.unidad, c.resumen, c.precio, c.fecha, c.tipo, t.texto, 
MATCH (t.texto) AGAINST ('texto1 texto2 texto3') AS score
FROM precioc AS c
INNER JOIN preciot AS t ON c.codigo = t.codigo
WHERE
MATCH (t.texto)
AGAINST ('texto1 texto2 texto3')
AND c.codigo LIKE'D_______'
AND c.codigo NOT LIKE'D50_____'
ORDER BY score
LIMIT 40
 

sin ordenación Mostrando registros 0 - 39 (40 total, La consulta tardó 5.0002 seg)

ha bajado de forma considerable, pero es todavía nada operativa.

¿alguna sugerencia?

un saludo,
josé carlos.

[gnzsoloyo]

¿alguna sugerencia?

Una, si quieres intentarla... dos queries:

Código MySQL:

CREATE TEMPORARY TABLE tablascore
SELECT c.clave, c.codigo, c.unidad, c.resumen, c.precio, c.fecha, c.tipo, t.texto, 
MATCH (t.texto) AGAINST ('texto1 texto2 texto3') AS score
FROM precioc AS c
INNER JOIN preciot AS t ON c.codigo = t.codigo
WHERE
MATCH (t.texto)
AGAINST ('texto1 texto2 texto3')
AND c.codigo LIKE'D_______'
AND c.codigo NOT LIKE'D50_____'
LIMIT 40;
 

Código MySQL:

SELECT *
FROM tablascore
ORDER BY score
 

Cuidado: Deben ejecutarse en forma seguida, y sin que se cierre la conexión.

Cuando la conexión se cierre o se interrumpa, esta tabla temporal se borrará sola. Además sólo el usuario que está en esta conexión puede verla (ventajas de las tablas temporary), y no importa si dos usuarios o más crean tablas con el mismo nombre.

Personalmente lo implementaría desde un SP, así hago una sola llamada.

[evoarte]

una cosa más he de añadir, he comprobado el resultado y está ordenado por defecto por el campo score, por lo que no es necesario utilizar ORDER BY. Por lo tanto hemos pasado de 57 a 5s.

Pero hay que seguir mejorando, y no se por donde.

un saludo,
josé carlos.

[gnzsoloyo]

Prueba lo que te digo, e incluso te sugiero que lo hagas por SP.
Por otro lado, has reducido a un décimo el tiempo. Para un indice FULLTEX... es una buena diferencia.

[evoarte]

pues aunque no muestra el resultado, utilizo phpmyadmin, creo que no hay mejora del rendimiento, pues la ventana se refresca más o menos a los 4 ó 5 segundos.

muchas gracias, pero necesito mayor rápidez de respuesta.


un saludo,
josé carlos.

[evoarte]

hola,

He probado la consulta, haciéndola en phpMyAdmin no devuelve ningún resultado, y ejecutándola desde asp clásico devuelve un error de sintaxis (cerca del punto y coma: ;).

Código MySQL:

CREATE TEMPORARY TABLE tablascore 
select c.clave, c.codigo, c.unidad, c.resumen, c.precio, c.fecha, c.tipo, t.texto, 
MATCH(t.texto) AGAINST('texto1 texto2 texto3') AS score 
from precioc AS c 
INNER JOIN preciot AS t ON c.codigo = t.codigo 
where 
MATCH(t.texto) AGAINST('texto1 texto2 texto3') 
and c.codigo LIKE 'D_______' 
and c.codigo NOT LIKE 'D50_____' 
LIMIT 40;
 
select * from tablascore order by score
 

no se por donde seguir,

gracias. un saludo.

[evoarte]

Ya lo tengo : Mostrando registros 0 - 39 (40 total, La consulta tardó 0.0035 seg)

He creado un índice para cada campo que se utiliza para unir las tablas en las consultas, en este caso los campos codigo de las 2 tablas precioc y preciot.

un saludo,
josé carlos.