12.3.04
Optimizing SQL queries really work
I've always knew the sql queries I used on this site were shoddy. I never really tripped off of it because "it worked" and it's not like the traffic coming here is taxing my server in the least.
Well, I recently decided to try to clean them up a bit, not only for learning purposes, but to see what performance benefit is really possible.
Just as a simple test, I changed the way my index page queried the catagory count on the left. Previously, I used one query to grab every distinct catagory in the table, and using a while loop, ran each result though another query to count the rows associated with each catagory. Using MySQL Administrator I seen that the whole page used 26 queries.
To change, I now do the following:
SELECT DISTINCT table.row, COUNT(table.row) AS count FROM table GROUP BY table.row
This lowered the page queries to 10. Impressive. I still have some more to clean up, but seeing the potential optimizing has made me a believer.
11:39 pm in updates | 1 person viewing0 ramblings so far