Intento optimizar una consulta sql lenta

[Illien]

Buenas tardes!
Tengo un problemilla con una consulta sql y es que es demasiado lenta. No tengo mucha experiencia en el campo y espero que alguien de por aquí pueda darme algún consejo útil.

Es una base de datos de películas y el objetivo de la consulta es hacer una búsqueda general de películas en todas las tablas a partir de un texto que escriba un usuario.
Es decir, que si el usuario escribe 'algo', aparezcan todas las pelis que tengan en el titulo 'algo', pero también todas las pelis que tengan asociadas algún actor que contenga 'algo' y así con todos los datos.

La consulta es esta:

Código:

SELECT DISTINCT pelicula.titulo,pelicula.año,pelicula.duracion,pelicula.pais 
FROM pelicula,es_actor_pelicula,es_director,idioma_pelicula,subtitulos_pelicula,tiene_pelicula,ubicacion_pelicula
WHERE 
(pelicula.titulo REGEXP '.*consulta.*') 
OR (pelicula.año REGEXP '.*consulta.*') 
OR (pelicula.pais REGEXP '.*consulta.*') 
OR (pelicula.notas REGEXP '.*consulta.*') 
OR (es_actor_pelicula.nombre REGEXP '.*consulta.*' AND pelicula.titulo= es_actor_pelicula.tituloPelicula AND pelicula.año = es_actor_pelicula.añoPelicula) 
OR (es_director.nombre REGEXP '.*consulta.*' AND pelicula.titulo= es_director.tituloPelicula AND pelicula.año = es_director.añoPelicula) 
OR (idioma_pelicula.idioma REGEXP '.*consulta.*' AND pelicula.titulo= idioma_pelicula.tituloPelicula AND pelicula.año = idioma_pelicula.añoPelicula) 
OR (subtitulos_pelicula.subtitulos REGEXP '.*consulta.*' AND pelicula.titulo= subtitulos_pelicula.tituloPelicula AND pelicula.año = subtitulos_pelicula.añoPelicula) 
OR (tiene_pelicula.productora REGEXP '.*consulta.*' AND pelicula.titulo= tiene_pelicula.tituloPelicula AND pelicula.año = tiene_pelicula.añoPelicula) 
OR (ubicacion_pelicula.ubicacion REGEXP '.*consulta.*' AND pelicula.titulo= ubicacion_pelicula.tituloPelicula AND pelicula.año = ubicacion_pelicula.añoPelicula)

Ya sé que son muchas cosas para una misma consulta.... pero con pocos datos iba bien, hasta que metí más de 10 películas :(
Espero que alguien pueda echarme una mano para optimizarla, porque hace poco que trabajo con sql.
Muchas gracias!! :D

[leonardo_josue]

Hola Illien:

Hay varias cosas que puedes mejorar en tu consulta...

Primero, no listes las tablas en el FROM ni hagas las uniones en el WHERE... en lugar de eso utilizas JOINS, son más eficientes:

Código:

En lugar de esto:

FROM tabla1, tabla2 
WHERE tabla1.campo = Tabla2.campo

Haz esto:

FROM tabla1 
INNER JOIN tabla2 on tabla1.campo = tabla2.campo:

Segundo, hasta donde recuerdo, las comparaciones con REGEXP, tal como las estás poniendo son más lentas que las comparaciones con LIKE '%consulta%'... podrías probar a cambiar estas condiciones... de cualquier manera este tipo de comparaciones SON DE LAS MÁS INEFICIENTES QUE EXISTEN, por lo que deberías tratar de evitarlas a toda costa...

Tercero, verifica que tengas definidos índices en todas tus tablas, tengo un poco de miedo de preguntar, pero al ver esto:

Código:

pelicula.titulo = es_actor_pelicula.tituloPelicula AND

Puedo suponer que estás manejando campos VARCHAR como llaves, sería más sencillo que manejaras índices numéricos como tus campos llaves, en lugar de cadenas...

También tendrías que aclararnos, necesitas realmente incluir todas las tablas en el criterio de búsqueda???, es decir, podrías implementar un tipo de filtro, para que el usuario te definiera sobre qué tabla quiere buscar (titulo, director, idioma, etc.)

Con las recomendaciones anteriores, la consulta podrías probarla más o menos así:

Código MySQL:

SELECT DISTINCT 
  pelicula.titulo,
  pelicula.año,
  pelicula.duracion,
  pelicula.pais 
FROM pelicula,
INNER JOIN es_actor_pelicula ON
  pelicula.titulo = es_actor_pelicula.tituloPelicula AND
  pelicula.año = es_actor_pelicula.añoPelicula
INNER JOIN es_director ON
  pelicula.titulo = es_director.tituloPelicula AND 
  pelicula.año = es_director.añoPelicula
INNER JOIN idioma_pelicula ON
  pelicula.titulo = idioma_pelicula.tituloPelicula AND 
  pelicula.año = idioma_pelicula.añoPelicula
INNER JOIN subtitulos_pelicula ON
  pelicula.titulo = subtitulos_pelicula.tituloPelicula AND 
  pelicula.año = subtitulos_pelicula.añoPelicula
INNER JOIN tiene_pelicula ON
  pelicula.titulo= tiene_pelicula.tituloPelicula AND 
  pelicula.año = tiene_pelicula.añoPelicula
INNER JOIN ubicacion_pelicula ON
  pelicula.titulo = ubicacion_pelicula.tituloPelicula AND 
  pelicula.año = ubicacion_pelicula.añoPelicula
WHERE 
  pelicula.titulo LIKE '%consulta%' OR 
  pelicula.año LIKE '%consulta%' OR 
  pelicula.pais LIKE '%consulta%' OR 
  pelicula.notas LIKE '%consulta%' OR 
  es_actor_pelicula.nombre LIKE '%consulta%' OR 
  es_director.nombre LIKE '%consulta%' OR 
  idioma_pelicula.idioma LIKE '%consulta%' OR 
  subtitulos_pelicula.subtitulos LIKE '%consulta%' OR 
  tiene_pelicula.productora LIKE '%consulta%' OR 
  ubicacion_pelicula.ubicacion LIKE '%consulta%'
 

Finalmente, puedes considerar el hacer un sólo catálogo tipo FULL TEXT con toda la información de tus tablas, en lugar de tener un modelo con 7 tablas... e implementar las funciones de búsqueda de texto completo. Dale un vistazo a esta liga:

http://dev.mysql.com/doc/refman/5.0/...xt-search.html

Saludos
Leo.