mi Consultaa, muy lenta

[xxxivanxxx]

hola a todos, bueno tengo un problema con una consulta ya que me demora demaciado y consume mucho CPU, por favor espero que puedan ayudarme..

mi consulta es la sgte:

SELECT a.artista,a.alink,c.cancion,c.slink,c.id_file,g.gl ink FROM artistas a, canciones c, generos g WHERE c.id_artista = a.id_artista AND a.id_genero = g.id_genero AND g.glink LIKE 'generodeprueba' ORDER BY c.rating DESC LIMIT 0,124

Resulta que se ejecuta con este tiempo: 0.8226 y el CPU se pone a 100%

no se mucho pero busque para ver los problemas y encontre "EXPLAIN" y lo ejecute y tengo los sgtes resultados:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL NULL NULL NULL NULL 92034 Using filesort
1 SIMPLE a eq_ref PRIMARY,id_artista PRIMARY 4 mydb.c.id_artista 1
1 SIMPLE g eq_ref PRIMARY,id_genero PRIMARY 4 mydb.a.id_genero 1 Using where

creo que es por el numero de filas que tiene la tabla canciones(92034), por favor si alguien me puede dar una solucion para que por lo menos no demore tanto o consuma menos CPU esa consulta, ya que he buscado pero no he encontrado ninguna solucion .. gracias :D

[huesos52]

INNER JOIN amigo.
Esa forma de anidar las tablas es del estandar SQL92

[xxxivanxxx]

gracias pero podrias poner como seria la consulta es que no se mucho de la sintaxis .. te lo agradeceria

[huesos52]

Código MySQL:

SELECT 
a.artista,
a.alink,
c.cancion,
c.slink,
c.id_file,
g.gl ink 
FROM artistas a
INNER JOIN
canciones c
ON a.id_artista=c.id_artista
INNER JOIN
generos g
ON g.id_genero=a.id_genero
WHERE  g.glink LIKE 'generodeprueba' 
ORDER BY c.rating DESC 
LIMIT 0,124
 

[xxxivanxxx]

muchas gracias pero me demora lo mismoo y lo del CPU esta igual ... tal vez la tabla no estee bien creada, por favor alguien que me ayudee

[gnzsoloyo]

Uno de los problemas que tienes es que estás usando dos de los peores tipos de cláusulas para la performance: LIKE y ORDER BY. Ambos son algunas de las cosas que más recursos consumen.
Por otro lado, LIKE tiene mucho sentido cuando buscas algo que comienza, termina o contiene un pedazo de texto, pero tu estás en realidad buscando algo que es igual a una cadena (no tienes los comodines de cadena), por lo que usar LIKE en ese contexto carece de sentido y sólo le agregas ineficiencia a la consulta.
Además, si para tí "rating" es un criterio de ordenamiento, bien podrías crear un índice sobre esa columna. Eso podría ayudar.
Otra posibilidad (si es que ese campo no es PK) sería un índice en ambas tablas sobre "artista", ya que es campo de enlace. Si es PK y FK al mismo tiempo, no se necesita.

[xxxivanxxx]

gracias por responder, pero entonces que puedo usar en vez de LIKE y ORDER BY, buenoo o siempre va a demorar y consumir CPU por el numero de registros, aqui te dejo la estructura de las tablas, si me puedes ayudar es que no entiendo mucho sobre lo que escribiste,

tabla canciones: 92034 registros!

Código MySQL:

CREATE TABLE `canciones` (
 `id_cancion` int(11) NOT NULL DEFAULT '0',
 `id_disco` int(4) DEFAULT NULL,
 `id_artista` int(6) NOT NULL DEFAULT '0',
 `cancion` varchar(100) NOT NULL,
 `slink` varchar(150) NOT NULL,
 `id_file` char(38) NOT NULL,
 `play` int(6) NOT NULL DEFAULT '0',
 `rating` int(6) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id_cancion`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 

tabla artistas:

Código MySQL:

CREATE TABLE `artistas` (
 `id_artista` int(6) NOT NULL DEFAULT '0',
 `id_genero` int(6) NOT NULL DEFAULT '0',
 `artista` varchar(60) NOT NULL,
 `alink` varchar(100) NOT NULL,
 `views` int(6) NOT NULL DEFAULT '0',
 `rating` int(6) NOT NULL DEFAULT '0',
 `ntemas` int(3) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id_artista`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 

tabla generos:

Código MySQL:

CREATE TABLE `generos` (
 `id_genero` int(6) NOT NULL AUTO_INCREMENT,
 `genero` varchar(60) NOT NULL,
 `glink` varchar(60) NOT NULL,
 PRIMARY KEY (`id_genero`)
) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=latin1
 

y la consulta en cuestion que me demora mucho:

Código MySQL:

SELECT a.artista,a.alink,c.cancion,c.slink,c.id_file,g.gl ink FROM artistas a, canciones c, generos g WHERE c.id_artista = a.id_artista AND a.id_genero = g.id_genero AND g.glink LIKE 'generodeprueba' ORDER BY c.rating DESC LIMIT 0,124
 

si el problema es la estructura, podrias ayudarme en como puedo hacer la estructura para no tener problemas con ese tipo de consultas, :)

[gnzsoloyo]

Prueba:

Código MySQL:

SELECT 
     a.artista,
     a.alink,
     c.cancion,
     c.slink,
     c.id_file,
     g.gl ink 
FROM 
     artistas a 
     INNER JOIN 
     generos g  ON a.id_artista = c.id_artista  
     INNER JOIN canciones c ON a.id_genero = g.id_genero 
WHERE g.glink  = 'generodeprueba' 
ORDER BY c.rating DESC 
LIMIT 0, 124;
 

Luego crea un índice en Artistas sobre el campo id_genero, y otro en Géneros sobre glink y vuelve a probar.

[xxxivanxxx]

solo con esa consulta me demoro: 1.9459
luego creando los indices este fue el tiempo: 1.8887
bueno el tiempo no importa tanto pero el consumo de CPU si, ya que llega a 100% cuando hago la consulta, y si varios usuarios hacen la misma consulta al mismo tiempo, creo que se supercargaria el servidor.

[gnzsoloyo]

Los servidores están preparados para manejar las consultas concurrentes, y además no tienen un único microprocesador ni un sólo disco. SI no fuese así nadie trabajaría con MySQL...

Si quieres seguir puliendo, se puede, pero hay que revisar bien las estructuras, las relaciones, los tipos de tabla, realizar selecciones intermedias, etc.

[gnzsoloyo]

Por ejemplo:

Código MySQL:

SELECT 
     a.artista,
     a.alink,
     c.cancion,
     c.slink,
     c.id_file,
     g.gl ink 
FROM 
     artistas a 
     INNER JOIN 
     (SELECT idgenero, glink  FROM generos WHERE glink  = 'generodeprueba') g  
         ON a.id_artista = c.id_artista  
     INNER JOIN canciones c ON a.id_genero = g.id_genero 
ORDER BY c.rating DESC 
LIMIT 0, 124;
 

[xxxivanxxx]

muchas gracias, entonces ese tiempo estaria bien para la consulta y si 100 o más usuarios la realizaran al mismo tiempo, no habria problemas con el servidor, solo quiero saber eso, o si se puede arreglar cambiando la estructura de las tablas.

[gnzsoloyo]

Todo es optimizable, pero las consultas tienen un límite.
En el caso de MySQL, normalmente la instalación básica está configurada para usar poco hardware o de bajo rendimiento. Si te vas al manual de referencia encontrarás capítulos y documentos dedicados al tema de la optimización.
A nivel de diseño de la base, la elección incorrecta de los tipos de columna puede impactar negativamente en la performance, así como una incorrecta selección de índices.

El tema es largo:
- Optimizing MySQL.
- MySQL optimization.

[xxxivanxxx]

bueno me puse a leer un poco de esos manuales, pero ahy disen para tablas mayores a 2Gb eso quiere decir con millones de registros, pero la mia solo tiene 100 000, y son solo 3 tablas, y solo la que tiene esos registros es la tabla canciones, y no creo que sea algo super complicado ya que solo quiero obtener datos de las 3 tablas tal vez la estructura de mi Base de Datos estee mal creada, tal vez me puedas dar un consejo sobre como crear las tablas de nuevo para poder realizar las consultas sin ese problema o juntando las tres tablas en 1 sola(que habia echo antes) ahy no demora nada una consulta, pero eso no es lo que quiero por que no puedo realizar muchas cosas.. QUIERO AYUDAA!! por favor..

[gnzsoloyo]

Bueno, trata de escribir calmado, ponle puntos aparte al texto y si logramos leerlo bien, te podremos ayudar.

Para poder empezar, si hay que ver la estructura de la base de datos, deberíamos tener una idea de cómo está armada, qué tablas, con qué campos, con qué relaciones, con cuáles datos y por qué dices que 1,8 a 1,9 segundos es demasiado tiempo para responder una consulta que barre más de 100.000 registros...
Además no resulta claro en qué contexto de hardware y sobre qué sistema operativo estás haciendo las pruebas, y si es el tiempo de MySQL o está interviniendo también codficación en algún lenguaje, etc.
Como habrás visto (si es que miraste alguno de los links que te pasé), hay muchos considerandos a realizar a la hora de optimizar las consultas... No solamente el tamaño de las tablas.
Para darte una idea, una base con una tabla de 30 Mb (muy lejos de los 2 Gb que mencionas), si la consulta no está optimizada, y dependiendo de los datos cruzados, puede tardar hasta 2 minutos y medio en responder. Y eso puede en definitiva ser un buen tiempo... dependiendo de lo que se haga.
No te guíes por el tamaño en bytes. Eso es sólo una parte del problema.

Empecemos por una descripción del entorno de hardware y software que usas. Luego postea toda la estructura de la base, o bien su diagrama de tablas. A partir de allí veremos.