[geeklog-devel] New block on geeklog.net

Blaine Lang geeklog at langfamily.ca
Sat Jan 24 20:07:55 EST 2004


If you are in the development group on geeklog.net you will notice a new block called quickstats. I added it to show some high level information of the activity on the site. I noticed this morning that we had over 600 downloads in the last 24 hours and wanted to know what files were popular.

This block now show the top 5 downloads as well as the total over the past 24hr window. 
It's adding 2-3 seconds to refresh the page now and I've added a couple indexes but it still has a lot of records to crunch thru to calculate counts.

SELECT count(*) AS downloads,downloads.lid, filedetail.title FROM {$_FM_TABLES['filemgmt_history']} downloads, {$_FM_TABLES['filemgmt_filedetail']} filedetail WHERE filedetail.lid = downloads.lid AND unix_timestamp(downloads.date )  + 86400 > unix_timestamp(  ) GROUP  BY lid ORDER  BY downloads DESC LIMIT 5"

Maybe someone can suggest a better SQL statement.
I have indexes on lid (both tables) and date.

If we can't make it run any faster and you want to disable it, then I can enable it just for myself.

Blaine

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20040124/33c4dd13/attachment.html>


More information about the geeklog-devel mailing list