3.31.05
Further SQL Optimizations
Well, time came to do that extra clean up work here. At my last post, I was able to get the number of database queries for hits to my index page down to 10. That came with a simple tuning of how my catagory lists on the right pulled it's data. But, I neglected to do anything with these actual posts and comment count.
Each time an entry was pulled from the database, a second query would check to see how many comments were available for it. Though I only show 5 entries on the index page, the catagory pages used the same query method. That means, catagories that have 40+ posts will end up doing 40+ queries...only to increase as time goes on.
Using a method similar to the catagory query, I now use a query somewhat like this:
SELECT table1.*, COUNT(table2.cID) FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.cID GROUP BY table1.id
This has dropped my index page queries down to 5 and catagory page down to 3 (the additional two queries per page being maintenance records). Schweet!
2:32 pm in updates | 1 person viewing0 ramblings so far