Did some optimising of Hype this weekend. When I wrote the new version I didn’t think at all about query efficiency, I used DB_DataObject to handle all the SQL and just got on with implementing the functionality required. When I came to do some optimisation the first thing I looked at was the number of queries that were actually being run. Turns out it’s about 100 for the home page. Those portlets are data hungry!

The obvious candidate for optimisation was the high scores portlet. The query behind that did a count on the messages for a group (>36,000 for Class of 2001) and grouped them by user. It was taking at least 2.5 seconds for the big group. Once I eliminated that, the other queries take no time at all. Less than a hundredth of a second for a select.

So the actual mechanism of querying MySQL from PHP itself is pretty efficient. The queries themselves can be slow however. I’m going to be timing queries on the MySQL command line from now on, but I won’t be thinking of perfomance too much when I next need to trawl something from the DB.

If you’re interested at looking at some profiling info for Hype, add ?debug=true to any page URL and check out the tail of the HTML source.

Note: My MySQL server and web server aren’t even on the same box, but they are in the same rack.