John Beales

Here’s the brick book that’s been keeping me from reading some other design & HTML books since Christmas, and I’m finally done! Despite its size, I wanted to read High Performance MySQL from cover to cover as I’ve been working a lot recently with some databases that could stand to go faster, and, while it was a long read, (made longer by taking breaks and reading other books), it was very useful, and will never be too far from my desk, especially when I’m working on server-side code and database design and tuning.

Even before I finished the High Performance MySQL my work was seeing the benefits of what I had learned partway through the book. There are a lot of quirks in MySQL that I wasn’t aware of, especially when it comes to joining tables and applying indexes. In one instance I was able to take a query that had been taking several minutes to execute and bring its execution time down to just a few seconds simply by rewriting some joins in such a way that, when thinking only about how data is related, appears to be quite bizarre, but when thinking about how a query will be executed by MySQL makes good sense.

I learned about how indexes are used, not just in theory but actually how MySQL looks for something within an index, and I learned about how I can use indexes to make it so MySQL doesn’t have to touch the underlying table data for some SELECT queries, (quite the speed enhancement there). I learned more about InnoDB than I thought I would know for quite some time. There’s a great appendix about the Sphinx search server, which is a product that I’ve been interested in for quite a while. The appendix gave me the introduction that I needed, and I hope to start using Sphinx to power some searches soon.

If you work with MySQL databases a lot, and especially if you are involved in designing them, then High Performance MySQL is a very good book to have on hand. Even if you don’t read it from cover to cover like I did it’s a great reference and will help you speed up your MySQL instances.

Want to read it yourself? Get it from one of these places and I’ll receive a small kickback:

Amazon.com (USA) Amazon.ca (Canada) Amazon.co.uk (UK)

As I mentioned yesterday, I’m doing some work in WordPress right now, and a few minutes ago I tweeted that my custom code is messing with WP’s wp_get_archives() and wp_list_categories() functions, well, I found the problem.

I am including the 4RoadService.com header & utilities files in my WordPress theme, and I am using the same user here on my test server for both the main 4RoadService.com database and the WordPress database. It turns out that when the 4RoadService.com database connection was initialized, since it uses the same connection info as the WordPress database, the existing connection was just reused, (this behaviour, by the way, is well described in the PHP documentation), then when the 4RoadService.com connection was told to use the main 4RoadService database it did, thus switching our one and only connection away from the WordPress database, and making WordPress think that there were no posts on the blog.

Fortunately, there is a quick workaround, just add one more attribute to the mysql_connect() function so it looks like this:

$dblink = mysql_connect($host, $user, $pass, true);

This way a new connection is established, and the WordPress connection is left alone.

I am left wondering why, in the loop, WordPress was able to see my posts, perhaps it establishes a second database connection in there. However, I’m not going to spend the afternoon poking through the guts of WordPress.