Se puede simplificar esta sentencia?...

[talle]

Tengo un script de directorio que en general funciona bien, pero lo que son las secciones de "últimos enlaces", "top enlaces" y "enlaces populares", tardan una eternidad en cargar, y creo que es por la enorme complejidad de la sentencia que se usa para obtener los registros. A continuación os pongo cómo ejemplo la de "Top enlaces" en la que se solicita a la base de datos los 10 enlaces más valorados con el número de clics y número de comentarios de cada uno.

SELECT t1.*, COUNT(DISTINCTROW(t2.`id`)) clicks, '0' id_editor_edit,COUNT(DISTINCTROW(t3.`id`)) comments, t4.`path` path FROM `dir_links` t1 LEFT JOIN `dir_link_clicks` t2 ON t1.`id` = t2.`id_link` LEFT JOIN `dir_link_comments` t3 ON t1.`id` = t3.`id_link` LEFT JOIN `dir_categories` t4 ON t1.`id_category` = t4.`id` WHERE t1.`status` = 'active' AND `id_category` != '333' GROUP BY t1.`id` ORDER BY t1.`rank` DESC LIMIT 0, 10;

No se podría simplificar esta sentencia?...

Gracias.

[jurena]

perdón,
sin darme cuenta repetí el mensaje. Mira el siguiente.

[jurena]

claro que sí. Dinos cómo se llaman todos los campos implicados y los datos que incluyen; acláranos las relaciones. También los parámetros de búsqueda tras el where, etc.

La clave estar en buscar primero los id de los 10 más valorados y luego mediante un cruce, traerte sólo los datos concretos de esos ya seleccionados. No sé qué base (motor) usas, si Oracle, MySQL, SQLserver, etc. Dínoslo también.

[talle]

Uso MySQL.

Campos implicados para el SELECT de "Top enlaces":

Tabla dir_links: id, id_category, status, rank

Tabla dir_categories: id, path

Tabla dir_link_clicks: id_link

Tabla dir_editors: id (esta tabla no es importante, se puede evitar)

Tabla dir_link_comments: id_link

La sentencia debe seleccionar ordenados de mayor a menor los 10 enlaces (dir_links.id) activos (dir_links.status = 'active') con mayor calificación (dir_links.rank), mostrando:

-título (dir_links.title)
-localización geográfica (dir_links.location)
-descripción (dir_links.description)
-url del enlace (dir_links.url)
-calificación (dir_links.rank)
-fecha de alta (dir_links.date)
-número de clicks (contar dir_links_clicks.id_link)
-número comentarios (contar dir_links_comments.id_link)

Datos adicionales necesarios:

-id de la categoría (dir_links.id_category)=(dir_categories.id)
-url de categoría (dir_categories.path) para formar la url para ver más destalles del enlace.

Hay que añadir al WHERE que no se muestren enlaces de la categoría 333 (dir_categories.id != 333)

Campos implicados para el SELECT de "Últimos enlaces":

Lo mismo que para el top pero ordenando los registros de mayor a menor por la fecha de alta (dir_links.date)

Campos implicados para el SELECT de "Más visitados":

Lo mismo pero ordenado los registros de mayor a menor por el número de clicks, contar (dir_links_clicks.id_link)=(dir_links.id)

--------

Dime si necesitarías más datos.

Gracias.

[jurena]

La única duda que tengo es cómo generas el dato de rank.
Dinos cuál es su naturaleza y cómo lo obtienes...

[talle]

Nada, el rank lo introduce manualmente un editor es sólo un número que luego se transforma en un gráfico mediante un script.

[jurena]

Perdona que te pregunte tanto pero necesito saber lo que buscar. Si tú estableces el rank y limitas por el rank, eso es una cosa; otra es que ordenes por la suma de clics y comentarios, o por los clics y luego por los comentarios. Necesito saber la razón del orden de manera precisa. ¿Cuál es el criterio de ordenación, el rank, el número de clics + número de comentarios, el número de clics y luego de comentarios? Acláranos eso y ya podremos empezar a buscar una consulta con mejor rendimiento.

[talle]

Para "Top enlaces" se debe establecer la ordenación principal por el rank (dir_links.rank) de mayor a menor y ordenación secundaria por número de clicks (contar dir_links_clicks.id_link) descendente.

Para "Últimos enlaces" al ordenación debe ser descendente por fecha de alta (dir_links.date) sin más. En todo caso se podría añadir una ordenación secundaria por rank.

Y para "Más visitados" por nº de clicks descendente y cómo orden secundario por rank.

[jurena]

Para el top 10, prueba esta, y me dirás el rendimiento:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN ( 
 
SELECT dl.id
FROM `dir_links` dl
WHERE dl.id_category !=333 AND dl.STATUS = 'active'
ORDER BY rank DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.rank DESC , totallinks DESC
 

Esto es para que te hagas una idea. Te iré añadiendo las otras en este post, pero es posible que las saques tú aplicando el mismo sistema...

Para últimos enlaces:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks, dl.STATUS
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN ( 
 
SELECT dl.id
FROM `dir_links` dl
WHERE dl.id_category !=333 AND dl.STATUS = 'active'
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.fecha_de_alta DESC
 

Para los más visitados

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, dl.STATUS, t1.tot AS totallinks
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN ( 
 
SELECT dl2.id, COUNT( dlc.id_link ) AS tot
FROM `dir_links` dl2
INNER JOIN dir_link_clicks dlc ON dl2.id = dlc.id_link
WHERE dl2.id_category !=333
AND dl2.STATUS = 'active'
GROUP BY dlc.id_link
ORDER BY tot DESC 
)t1 ON dl.id = t1.id
ORDER BY totallinks DESC , dl.rank DESC
 

[talle]

Uffff.. muchas gracias, que trabajo te estoy dando.

Parece que funciona, pero he tenido que modificar dl.fecha_de_alta por dl.date y además no me muestra el número de clicks, ni el número de comentarios de cada enlace. Ahora también aparece un enlace para editores que antes no aparecía y la url para mostrar más detalles del enlace está casi correcta, pero no funciona porque le falta el id del enlace.

Mira, te pongo el código php original "Top enlaces" para ver si te sirve de ayuda:

Código PHP:

          $sql = "SELECT t1.*, COUNT(DISTINCTROW(t2.`id`)) clicks, ";
        $sql .= ($aEditor) ? "IF (t5.id_editor IS NULL, '0', '1') id_editor_edit," : "'0' id_editor_edit,";
        $sql .= "COUNT(DISTINCTROW(t3.`id`)) comments, t4.`path` path ";
        $sql .= "FROM `{$this->mPrefix}links` t1 ";
        $sql .= "LEFT JOIN `{$this->mPrefix}link_clicks` t2 ";
        $sql .= "ON t1.`id` = t2.`id_link` ";
        $sql .= "LEFT JOIN `{$this->mPrefix}link_comments` t3 ";
        $sql .= "ON t1.`id` = t3.`id_link` ";
        $sql .= "LEFT JOIN `{$this->mPrefix}categories` t4 ";
        $sql .= "ON t1.`id_category` = t4.`id` ";
        if ($aEditor)
        {
            $sql .= "LEFT JOIN `{$this->mPrefix}categories_editors` t5 ";
            $sql .= "ON t1.id_category = t5.id_category AND t5.id_editor = {$aEditor} ";
        }
        $sql .= "WHERE t1.`status` = 'active' AND `id_category` != '333' ";
        $sql .= "GROUP BY t1.`id` ";
        $sql .= "ORDER BY t1.`rank` DESC ";    
        $sql .= $aLimit ? "LIMIT {$aStart}, {$aLimit}" : ''; 
   

Yo le he cambiado por el siguiente pero me da los errores que te he comentado.

Código PHP:

  $sql = "SELECT dl.title, dl.location, dl.url, dl.rank, dl.date, dl.description, dc.path, count( dlc.id_link ) totallinks
FROM {$this->mPrefix}links dl
INNER JOIN {$this->mPrefix}categories dc ON dl.id_category = dc.id
INNER JOIN ( 
SELECT dl.id
FROM `{$this->mPrefix}links` dl
WHERE dl.id_category !=333 AND dl.STATUS = 'active'
ORDER BY rank DESC ";
$sql .= $aLimit ? "LIMIT {$aStart}, {$aLimit}" : '' ;
$sql .= ")t1 ON dl.id = t1.id
INNER JOIN {$this->mPrefix}link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.rank DESC , totallinks DESC"; 
   

[jurena]

Talle,

no escribas código PHP en este foro. Prueba la consulta y dime qué te falta. Ya me he dado cuenta de que no te muestran el número de comentarios. Estoy tratando de acomodarte la consulta, pero no sé en cuáles de ellas quieres el número de comentarios.
En cualquier caso, cuando te la ponga, la pruebas en consola o en el phpmyadmin sin variables, y, si funciona, luego será cosa de acomodarla al PHP, y para eso tendrás que recurrir al foro PHP. Es allí donde podrán ayudarte mejor.

[talle]

Mil disculpas por poner código php, no estaba al corriente.

El número de comentarios aparece en las tres listas.

Ya he solucionado lo de la id del enlace en la url de "más detalles", añadiendo dl.id al primer SELECT.

También he conseguido que aparezca el número de clicks sustituyendo totallinks por clicks.

Me falta pues que se muestre el número de comentarios de cada enlace.

Lo del enlace para editores que me aparece ahora no sé cómo quitarlo, pero ya lo iré resolviendo por otro lado.

Gracias por todo.

[jurena]

talle,
La de los 10 últimos. He usado los datos y nombres que tengo yo en mi base. No he puesto date porque creo que es una palabra reservada y no debe usarse para campo.

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks, t1.totcom, dl.STATUS
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl.id, COUNT( * ) totcom
FROM `dir_links` dl
INNER JOIN dir_link_comments dlc ON dlc.id_link = dl.id
WHERE dl.id_category !=333
AND dl.STATUS = 'active'
GROUP BY dl.id
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.fecha_de_alta DESC
 

Por el rank

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks, t2.totcom
FROM dir_links dl INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (  SELECT dl.id FROM `dir_links` dl WHERE dl.id_category !=333 AND dl.STATUS = 'active' ORDER BY rank DESC LIMIT 10 )t1 ON dl.id = t1.id
INNER JOIN (SELECT dlco.id_link AS id, COUNT(dlco.id_link) totcom FROM dir_link_comments dlco GROUP BY dlco.id_link)T2 ON T1.id = t2.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id GROUP BY dlc.id_link ORDER BY dl.rank DESC , totallinks DESC
 

Cuando es por clicks

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, dl.STATUS, t1.tot AS totallinks, t2.totcom
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl2.id, COUNT( dlc.id_link ) AS tot
FROM `dir_links` dl2
INNER JOIN dir_link_clicks dlc ON dl2.id = dlc.id_link
WHERE dl2.id_category !=333
AND dl2.STATUS = 'active'
GROUP BY dlc.id_link
ORDER BY tot DESC 
)t1 ON dl.id = t1.id
INNER JOIN (
 
SELECT dlco.id_link AS id, COUNT( dlco.id_link ) AS totcom
FROM dir_link_comments dlco
GROUP BY dlco.id_link
)t2 ON t1.id = t2.id
ORDER BY totallinks DESC , dl.rank DESC
 

[talle]

Salvo algunos pequeños errores que he corregido, funcionan bien las tres consultas, pero lo que falla es que sólo se muestran los enlaces que tienen algún comentario, los que no tienen ningún comentario no aparecen. Supongo que con alguna pequeña modificación se arreglaría, pero "no doy con la tecla".

Lo que sí es verdad es que ahora va mucho más rápido.

Gracias.

[jurena]

Iniciado por talle

Salvo algunos pequeños errores que he corregido, funcionan bien las tres consultas, pero lo que falla es que sólo se muestran los enlaces que tienen algún comentario, los que no tienen ningún comentario no aparecen. Supongo que con alguna pequeña modificación se arreglaría, pero "no doy con la tecla".

Lo que sí es verdad es que ahora va mucho más rápido.

Gracias.

Eso debe ser porque si no tienen ningún comentario, el inner join no lo muestra. En lugar de INNER JOIN, pon LEFT JOIN antes del select que hace referencia a los comentarios y ya nos dirás.
Por cierto, ¿cuál ha sido la diferencia de velocidad entre una y otra consulta? Lo pregunto sólo por curiosidad.

[talle]

He probado el LEFT JOIN y el restraso en cargar las páginas se ha incrementado bastante en "Top enlaces" y "Enlace populares", y en "Últimos enlaces" no carga ni tras esperar más de un minuto.

La diferencia, entre las sentencias originales y las últimas que me has proporcionado, son unos cuantos segundos de tiempo de carga. No es que sea una gran diferencia, pero por poco que sea se agradece.

[talle]

Creo que voy a prescindir del dato de número de comentarios, pienso que es lo que relentiza la consulta.

[jurena]

Talle,
Sí, creo que ese es en parte el problema, pero la causa de la ralentización quizás no sea tanto el agrupar dos veces, sino el hecho de que tiene que hacer la agrupación dos veces de todos los datos. Me explico:
agrupar dos veces es un problema, y en el caso de orden por número de clicks es lógico que tengas que agrupar todos los datos de la tabla, pero el group by por comentarios no debería hacerse en toda la tabla, sino solamente de los datos ya sacados de la primera agrupación. Creo que podríamos intentar una consulta en la que una selección que recogiera los datos de la primera agrupación, se viera afectada por esta segunda agrupación. Intentaré hacer alguna prueba y si me funciona te la haré llegar para que la pongas a prueba con tus datos. No estoy seguro de nada de esto que te digo, pero si nadie nos corrige, las pruebas lo harán.

[talle]

La verdad jurena es que no sé cómo agradecerte las molestias que te estás tomando. Ya me da apuro seguir con el tema. Si quieres déjalo y quito el dato de los comentarios y zanjamos este asunto.

[jurena]

No se trata de poner o quitar, sino de probar y solucionar.
Prueba esta consulta, que, creo te mostrará los comentarios y los clicks. Selecciona por clicks. Si es más rápida (creo que lo será), habremos resuelto el problema, al menos para esta consulta:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, dl.STATUS, t1.tot AS totallinks, COUNT( dlco.id_link ) AS totalcom
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
LEFT JOIN dir_link_comments dlco ON dl.id = dlco.id_link
INNER JOIN (
 
SELECT dl2.id, COUNT( dlc.id_link ) AS tot
FROM `dir_links` dl2
INNER JOIN dir_link_clicks dlc ON dl2.id = dlc.id_link
WHERE dl2.id_category !=333
AND dl2.STATUS = 'active'
GROUP BY dlc.id_link
ORDER BY tot DESC 
)t1 ON dl.id = t1.id
GROUP BY dlco.id_link
ORDER BY totallinks DESC , dl.rank DESC
 

Dinos qué tal...

He editado para añadir un LEFT JOIN que permita mostrar los seleccionados aunque no tengan comentarios.

[talle]

Perfecto!!!, ahora sí.

[talle]

Sólo decirte una vez más que gracias por todo jurema.

Si hay algo que puede hacer por tí no dudes en pedírmelo.

Saludos.

[talle]

Iniciado por jurena

No se trata de poner o quitar, sino de probar y solucionar.
Prueba esta consulta, que, creo te mostrará los comentarios y los clicks. Selecciona por clicks. Si es más rápida (creo que lo será), habremos resuelto el problema, al menos para esta consulta:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, dl.STATUS, t1.tot AS totallinks, COUNT( dlco.id_link ) AS totalcom
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
LEFT JOIN dir_link_comments dlco ON dl.id = dlco.id_link
INNER JOIN (
 
SELECT dl2.id, COUNT( dlc.id_link ) AS tot
FROM `dir_links` dl2
INNER JOIN dir_link_clicks dlc ON dl2.id = dlc.id_link
WHERE dl2.id_category !=333
AND dl2.STATUS = 'active'
GROUP BY dlc.id_link
ORDER BY tot DESC 
)t1 ON dl.id = t1.id
GROUP BY dlco.id_link
ORDER BY totallinks DESC , dl.rank DESC
 

Dinos qué tal...

He editado para añadir un LEFT JOIN que permita mostrar los seleccionados aunque no tengan comentarios.

jurena, voy a abusar una vez más de tu generosidad. Me quedó pendiente una sentencia cómo esta para los 10 últimos enlaces . Me he dado cuenta que la que puse sólo me muestra los enlaces que tienen algún comentario y click. Los que no tienen ningún comentario no aparecen.

Bueno, no hay prisa ninguna, cuando puedas.

Gracias.

[jurena]

amigo Talle,
prueba esta consulta a ver si hace lo que quieres, pero pruébala a fondo, porque ya ha pasado algún tiempo desde que trabajé con estos datos y no estoy seguro de que salga todo como quieres:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, ifnull( COUNT( dlc.id_link ) , 0 ) totallinks, ifnull( t1.totcom, 0 ) totalcomen, dl.STATUS
FROM dir_links dl
LEFT JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl.id, COUNT( dlc.id_link ) totcom
FROM `dir_links` dl
LEFT JOIN dir_link_comments dlc ON dlc.id_link = dl.id
WHERE dl.id_category !=333
AND dl.STATUS = 'active'
GROUP BY dl.id
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
LEFT JOIN dir_link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.fecha_de_alta DESC
 

[talle]

No sé... sólo me muestra 3 registros y los tres tienen 0 comments, pero no creo que sea importante lo de los comments, pues hay últimos registros con 0 comentarios que no aparecen.

[jurena]

Lo he editado de nuevo y he añadido un LEFT JOIN. Comprueba ahora, talle.

[talle]

Nada, el mismo resultado.

No entiendo que puede ocurrir, es muy extraño.

[jurena]

No te preocupes, no sucede nada extraño. Únicamente pasa que no lo hice bien. A ver si ahora.

Creo que así te funcionará para sacarlo por fechas:

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, ifnull( t1.totcli, 0 ) totallinks, ifnull( t1.totcom, 0 ) totalcomen, dl.STATUS
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl.id, COUNT( dlc.id_link ) totcom, COUNT( dlcli.id_link ) totcli
FROM `dir_links` dl
LEFT JOIN dir_link_comments dlc ON dlc.id_link = dl.id
LEFT JOIN dir_link_clicks dlcli ON dlcli.id_link = dl.id
WHERE dl.id_category !=333
AND dl.STATUS = 'active'
GROUP BY dl.id
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
 

Sólo quiero añadirte que todos los links deben tener categoría, claro. Si no fuera así, habría que cambiar uno de los INNER JOIN a LEFT JOIN.

[talle]

Ahora si que funciona, pero tarda una eternidad en mostrar los resultados.

[jurena]

Me equivoqué de estrategia. Esta creo que será más rápida, pues sólo agrupará y contará los de las 10 fechas más recientes.

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totcom, COUNT( dlcli.id_link ) totcli, dl.STATUS
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl.id
FROM `dir_links` dl
WHERE dl.id_category !=333
AND dl.STATUS = 'active'
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
LEFT JOIN dir_link_comments dlc ON dlc.id_link = dl.id
LEFT JOIN dir_link_clicks dlcli ON dlcli.id_link = dl.id
GROUP BY dl.id
ORDER BY dl.fecha_de_alta DESC