Optimizar consulta Fulltext

[natttt]

Hola.

Tengo un grave problema con las búsquedas en mi portal, tengo una base de datos con 400,000 registros (artículos) y al realizar una búsqueda con fulltext tarda 10 segundos o más bloqueando la base de datos. La consulta que hago ahora es, por ejemplo:

Código:

select u_id, u_titulo, u_url, u_report, u_fecha, u_votos, MATCH(u_titulo) AGAINST('Pink Floyd Breath') AS puntos from urls left join usuarios on u_usuario=us_id where MATCH(u_titulo) AGAINST('Pink Floyd Breath')  order by puntos desc, u_votos desc limit 0, 20

Me han comentado que usar LIKE es mucho más rápido, pero en mi caso no puedo, ya que el orden de las palabras no tendría que influir en los resultados.

Debo optimizar la consulta para que no sature el servidor pero por más que busque no encuentro cómo...

¿Alguien podría echarme una mano? Gracias!!

[leonardo_josue]

Hola natttt:

En realidad no hay mucho qué optimizar en tu consulta, pero revisa de cualquier manera algunos detalles:

1. La relación entre tus tablas realmente es opcional y necesitas un LEFT JOIN, o es mandatoria y en ese caso podrías utilizar un INNER JOIN.

2. revisa que tus campos suario y us_id estén definidos como índices.

3. la opción del LIKE podría servir, pero no tiene tantas ventajas en las búsquedas como lo tiene MATCH-AGAINST. podrías hacer una consulta así:

Código MySQL:

SELECT 
  u_id, u_titulo, u_url, u_report, u_fecha, u_votos
FROM urls 
LEFT JOIN usuarios ON u_usuario = us_id 
WHERE 
  u_titulo LIKE '%Pink%' OR
  u_titulo LIKE '%Floyd%' OR
  u_titulo LIKE '%Breath%'
ORDER BY u_votos DESC 
LIMIT 0, 20
 

Aunque aquí no podrías determinar qué resultado tiene mejor "puntuación" y de cualquier manera no te asegura que tenga mejores resultados que la consulta con MATCH-AGAINST... aun podrías mejorar esta consulta, por ejemplo cambiando los OR's por AND's, para traer sólo los títulos que contengan las tres palabras, independientes del orden... así como está, traería todos los títulos que contengan al menos una de las palabras.

Saludos
Leo.