Optimizar consulta sql con subconsultas

[djmourino]

Muy buenas a todos los foreros de forosdelweb, bueno vengo a preguntar por aquí haber si algún guru del sql me puede iluminar un poco con un script que estoy desarrollando.
Bueno lo primero explico lo que tengo, tengo 3 tablas.
usuarios = guardo el id del usuario ,nombre , apellidos ect.
mensajes= guardo los mensajes que le envió a los usuarios.
click = guardo los enlaces que pulsa el usuario en los mensajes que le envió.

Bien ahora lo que quiero conseguir es sacar el id del usuario y con este mismo hacer una subconsulta para saber el numero de mensajes que tiene pero solo los que hizo click, para que nos entendamos mejor pongo el script que tengo ahora mismo.

Código php:

$usuarios=Queryl("SELECT id FROM ".$prefix1."usuarios  WHERE estado='1' ORDER BY id");//Con esta consulta saco el id del usuario
 
while($datos_usuarios= mysql_fetch_array($usuarios)){
$id_usuario=intval($datos_usuarios['id']);
 
$usuario_mensajes_click=Queryl("SELECT a.*,b.* FROM ".$prefix2."mensajes AS a, ".$prefix2."click AS b 
                                  WHERE a.id_usuario='$id_usuario' AND b.id_mensaje=a.id 
                                  GROUP BY b.id_mensaje"); //El group es para que me cuente 1 solo resultado del mismo id_mensaje
 
$numero_=mysql_num_rows($usuario_mensajes_click);//Numero de mensajes que se le envió e hizo click
}
 

Bueno pos la idea es hacer eso mismo pero optimizando las consultas por que de esa forma que e puesto anteriormente si son 1000 usuarios el numero de consultas a la db se dispara, quiero hacer algo de este tipo

Código php:

$usuarios=Queryl("SELECT id,(SELECT count(*)  FROM ".$prefix2."mensajes ...ect) AS numero_mensajes_click  FROM ".$prefix1."usuarios  WHERE estado='1' ORDER BY id");
 

Un saludo y gracias.

[huesos52]

djmourino

Pon la consulta solo en código sql.
No utilices código php en este foro.

De esta forma es mas legible para los usuarios de este foro, atender tu solicitud.

Un saludo.

[djmourino]

huesos52 gracias por responder, pongo el código php para que vean lo que intento hacer por que quizás poniendo solo el código sql no se entienda bien , de todas formas no se si mi consulta estaría mejor en el foro de PHP (aun que el problema reside en la consulta sql no el php).

Un saludo.

[gnzsoloyo]

Precisamente es poniendo el código PHP que no se entiende.
A nosotros nos resulta más comprensible lo que intentas hacer leyendo el SQL, porque eso es a lo que nos dedicamos.
Borra todo el PHP y deja las consultas SQL; ponle valores posibles donde estás poniendo las variables y entonces veremos si la sintaxis está bien. Con tanto PHP entremezclado la mayoría de las veces se pierde el problema si es de SQL con tanto "ruido" visual.

[djmourino]

ok pongo entonces el sql.

Código sql:

SELECT id FROM  usuarios  WHERE estado='1' ORDER BY id
 

Con esa consulta sacaría el id del usuario.

Código sql:

SELECT a.*,b.* FROM  mensajes AS a, click AS b
WHERE a.id_usuario=id AND b.id_mensaje=a.id
GROUP BY b.id_mensaje
 

Con esta ultima quiero sacar el numero de mensajes que tiene x usuario dependiendo de si hizo algún click en los enlaces que tiene el mensaje y los clicks que hace se guarda en la tabla click se relaciona entre las dos mediante el id del mensaje.

Ejemplo:
En la tabla usuarios hay el usuario con el id 15 , bien sabiendo su id se consulta la tabla mensajes enlazándolas por el campo id_usuario, a la vez que cuento los mensajes que le e enviado limito este recuento verificando que en la tabla click existe el id del mensaje (si existe es que se hizo click si no existe no se cuenta el mensaje).

Seria fusionar esas dos consultas en 1 salo para "optimizar" la consulta y que el numero de consultas sea menor y si puede ser que sea mas rápido el calculo.

Lo que intento conseguir es algo así (el código que pongo ahora no funciona se me cuelga el apache).

Código sql:

SELECT id,(SELECT COUNT(*) FROM  mensajes AS a, click AS b WHERE a.id_usuario=id AND b.id_mensaje=a.id GROUP BY b.id_mensaje) AS numero_mensajes_click  FROM usuarios  WHERE estado='1' ORDER BY id
 

Un saludo, espero que así este mejor presentado el mensaje.

[huesos52]

Código mysql:

SELECT a.*,b.* FROM  mensajes a inner join click b
on  b.id_mensaje=a.id
WHERE a.id_usuario=id 
GROUP BY b.id_mensaje
 

Esta consulta es mucho mas optima que la que tienes.

Ahora, si lo que quieres es mostrar esto, para todos los usuarios con estado 1, prueba esto:

Código mysql:

SELECT a.*,b.* FROM  mensajes a inner join click b
on  b.id_mensaje=a.id
WHERE a.id_usuario in(select id FROM  usuarios  WHERE estado='1' ORDER BY id) 
GROUP BY b.id_mensaje
 

Un saludo

[djmourino]

El primer código me dio una "idea" de por donde encaminar la cosa, el segundo hace algo similar a lo que quiero, de todas formas tu código me ayudo a encontrar la llamada sql que "funciona".

Código sql:

SELECT id,(SELECT COUNT(*) FROM  mensajes AS a LEFT JOIN click AS b ON b.id_mensaje=a.id WHERE a.id_usuario=id GROUP BY b.id_mensaje) AS numero_mensajes_click FROM usuarios WHERE estado='1' ORDER BY id
 

Que me da como resultado esta llamada sql pues:

id (id del usuario) y numero_mensajes_click ( que seria el numero de mensajes que le envié y que hizo click), esos son los dos únicos datos que necesito sacar de la consulta sql, eso si aun que la consulta funciona bien tarda muchísimo en hacer el calculo, seguramente sea por que no esta del todo bien la consulta, haber si me la puedes ver.

Un saludo y gracias por la ayuda.


EDITO: Pues corrijo lo anterior, no funciona bien el codigo que e puesto anteriormente, me cuenta todos los mensajes y no excluye de la cuenta los que no se hicieron click seguramente no sea LEFT JOIN lo que tengo que usar, nunca se me dio bien estas cosas xD.