Archives For performance

In this edition…

Continue Reading…

Properly creating indexes on database tables is a key ability and one that many developers have little confidence in (i.e., it’s a common question I see). I’ve written about the subject in my books, on my blog, and in my newsletter, but I recently StumbledUpon a good, quick article on the subject over at Kyle’s Cousin that I thought I’d share. The article only dips its toe into the subject, but what it does well is two things. First, it demonstrates how you can confirm the effectiveness of indexes. And second, it explains what’s going on behind-the-scenes to better understand when and why indexes will be effective.

Some time ago I came across this post at Nettuts+ on the Top 20+ MySQL Best Practices. The ideas are simple, effective, and clearly explained. Well worth checking out if you use MySQL on a regular basis.

I’m in the process of writing my latest book, “Effortless E-commerce with PHP and MySQL”, and as part of the process of writing any book, there’s lots of research involved. I want to check that I’m saying the right technical things (of course), but I also want to make sure that I’m doing things in an optimal way, that I’m using all the features and resources available to me, that I’ve reflected any recent changes in technology, etc. During this process, I just came across this article on MySQL’s SQL Profiler: Using the New MySQL Query Profiler. I was looking for the best way to time the execution of various queries (specifically to compare straight SQL with stored procedures) when I saw this, and I was glad I did.

I’ll leave it up to you to read the full article, but the gist of it is that if you’re using MySQL 5.0.37 or greater and using the command-line mysql interface, you can enable profiling to see exact performance numbers for the queries you run. You can even see the nitty-gritty details: everything MySQL does to run the query, how long each step takes, and even what CPU or memory usage was required.

Between the SQL Profiler and the EXPLAIN the command, you can quickly improve how efficiently your SQL commands execute, which will improve the performance of your Web application as a whole. And, in a surprising result, I found that the stored procedures I created ran much, much faster than straight SQL (this was on MySQL 5.1.37). Clearly MySQL has been taking great strides to improve the performance of stored procedures, which used to be notoriously inefficient.

I StumbledUpon this tutorial on improving the load speed of your Web site. One interesting thing about this discussion is it starts with the DNS and how that factors into the speed: something I hadn’t considered. The tutorial also points out that download speeds have increased so much that it may be better to download fewer, larger resources (like all the CSS or JavaScript in one file) than more, smaller resources. Again, this is something I hadn’t thought about—I was still going with the old thinking of taking extra steps so that the user doesn’t have to download one iota of code more than they have to. The tutorial then goes into caching and compression, something I discuss in [intlink id=”167″ type=”post”]my series about applying YSlow![/intlink] to a site.

The tutorial goes on to talk about organizing HTML files: CSS at the top and JavaScript at the bottom. I’ve heard this before (about putting JavaScript right before the closing tag) but haven’t gotten around to testing it myself. It makes sense, though, as the browser doesn’t need the JavaScript to render the output for the user to see, and because the JavaScript code itself probably can’t do its thing until the HTML has loaded. If you keep reading, you’ll learn how to use CSS image sprites to minimize the number of images that have to be downloaded by the browser. My Web site has practically no images, so I haven’t used this myself, but it’s something that can dramatically improve the performance of a site.