Full Text Search: Problema al ordenar por relevancia, se tarda mucho

[xcars]

Hola amigos, comente en otro tema el problema que tengo con una BD de 10 millones de registros... ahora bien, si yo hago una consulta normal, del tipo

select nombre from mitabla where match(nombre, direccion) against('harold crow')

me devuelve las filas sin ningun problema y con una buena velocidad, ahora bien si quiero ordenar por relevanci por ejemplo

select nombre, match(nombre,direccion) against('harold crow') as relevancia where match(nombre,direccion) against('harold crow') order by relevancia desc

en este momento el proceso se tarda una eternidad y segun la teoria, esto no deberia de recargar el motor de mySql ya que el motor detecta que hay una operacion que es igual, entonces no entiendo que es lo que sucede, alguien que me pueda ayudar a resolver este problema, porque lo que sucede es que no aparecen ordenados por relevancia y cuesta ubicar la informacion que se busca.

Gracias de antemano

[emiliodeg]

es probable q necesites crear un indice, estos deben ser credos para las columnas q utilizamos para las busquedas

intenta eso a ver como va con el tiempo

[xcars]

Iniciado por emiliodeg

es probable q necesites crear un indice, estos deben ser credos para las columnas q utilizamos para las busquedas

intenta eso a ver como va con el tiempo

gracias por tu respuesta tengo indices creados para FULL-TEXT SEARCH (busquedas de texto completo) pero no estamos hablando de una tabla de 10,000 ni 100,000 registros, son 10 MILLONES de registros, y el problema no es con la busqueda, el problema es cuando quiero que la busqueda sea ordenada por relevancia.
Nose si alguno de uds. conoce sobre los INDEX TYPE (default, btree,rtree, hash), PACK KEYS.

El problema es que el rendimiento con menos de 1 millon de registros no se nota mucho, es decir cuando se tienen menos de 1 millon los resultados son casi imperceptibles, pero si hay diferencia, ya eso en una base de 10 Millones, si se puede notar... lo que no me deja de inquietar es que el motor deberia de detectar que esta ejecutando 2 instrucciones iguales (el match against para la puntuacion y para el where) he intentado eliminando el order by RELEVANCIA para ver si es por el order by el problema pero no veo cambio significativos..

Espero me puedan dar alguna luz

[xcars]

RETIRO LO DICHO SOBRE EL ORDER BY

efectivamente el order by es que me esta relentizando la busqueda hice otro tipo de busquedas, y el order by marca una diferencia de busquedas de 2 secs. a 7 min. aprox. como podria hacer??

[xcars]

al ver el explain

al usar order by me muestra como EXTRA filesorting que es ahi donde la busqueda se queda, ya qu el filesorting significa que debe recorrer fila x fila para ordenar correctamente,

¿que me pueden sugerir para evitar esto? recordemos que el valor de la relevancia se calcula al momento de ejecutar la busqueda no la tenemos en tabla ni nada por el estilo, por lo tanto no se puede crear un indice para ello, alguna sugerencia?

[xcars]

bueno, creo que lo resolvere por tablas temporales, aunque nuuunca lo he hecho, pero cuando tenga la solucion, con mucho gusto la posteare por si alguien algun dia tiene este problema.
Si alguien me podria ayudar con eltema de las tablas temporales, pues mucho ue mejor

[gnzsoloyo]

Primero verifica esto:
-¿La consulta procesa 10.000.000 de registros o devuelve 10.000.000 de registros?
En el primer caso, lo que deberías hacer es que primero realice la selección pedida y encerrarla en un paréntesis: (SELECCION), y a esa realizarle un ORDER BY: SELECT * FROM (CONSULTA) AS A ORDER BY CAMPOS;
De esta forma la tabla devuelta es una derivada y está en memoria, pero mucho menos voluminosa que el proceso original, sobre el que estás descargando el ORDER BY

En el segundo caso, el uso de tablas temporales (en disco) no te va a dar un resultado MUY bueno, te esztas olvidando del overheld del disco por el swapping que pedís.
verificá la optimización de FULL TEXT.

[xcars]

Iniciado por gnzsoloyo

Primero verifica esto:
-¿La consulta procesa 10.000.000 de registros o devuelve 10.000.000 de registros?
En el primer caso, lo que deberías hacer es que primero realice la selección pedida y encerrarla en un paréntesis: (SELECCION), y a esa realizarle un ORDER BY: SELECT * FROM (CONSULTA) AS A ORDER BY CAMPOS;
De esta forma la tabla devuelta es una derivada y está en memoria, pero mucho menos voluminosa que el proceso original, sobre el que estás descargando el ORDER BY

En el segundo caso, el uso de tablas temporales (en disco) no te va a dar un resultado MUY bueno, te esztas olvidando del overheld del disco por el swapping que pedís.
verificá la optimización de FULL TEXT.

saludos.
no es el caso I, hay 10 millones de registros en la base, intentare lo que me recomiendas en este momento, y te cuento...

[xcars]

sabes ahora releyendo lo que escribiste, creo que no se me apega tu recomendacion, te cuento nuevamente.
Lo que yo necesito hacer es una consulta de texto completa (FULL TEXT SEARCH) que me ordene los resultados por relevancia, entonces, en el order by de la relevancia es donde se queda mucho tiempo, ya que la instruccion ORDER BY lo que hace es recorrer todos los registros, uno por uno para ordenarlos, entonces lo que tengo que buscar, segun lo que creo, es tomar los datos de la consulta meterlos en una temporal, y luego esa temporal ya con el SCORE de la relevancia calculado, ordenarlos, ya solo tendria que recorrer, que 10,000 o 2000 registros para ordenar. Me parece raro si, porque el motor de MySQL deberia detectar que hay 2 instrucciones iguales, o nose si el problema es otro..
En fin segun tu recomendacion, no puedo hacer el order by, porque no tengo el valor por el cual voy a ordenar, que es la puntuacion que calcula MySQL de una busqueda de texto completo :( pero gracias por la info.