Para el top 10, prueba esta, y me dirás el rendimiento:
Código sql:
Ver originalSELECT 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:
Ver originalSELECT 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:
Ver originalSELECT 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