Database query optimization is a useful skill

Recently I read an article titled “Quick tip: Improve developer habits by showing time cost of DB queries” by Dan Birken.  He claims that one common reason why websites load slowly are because of unoptimized database queries:

Websites can be slow for many reasons, but an overwhelmingly common one is time spent performing database queries. Now database queries are likely a very important part of rendering the webpage – the information to populate a page has to come from somewhere. But sometimes rendering a page is slowed down by either unnecessary queries or improperly optimized queries.

I completely agree with his view.  For most web developers, querying information from a database is quite a common action.  Therefore, I believe learning how to write efficient and optimized database queries is a good skill to have.

I was fortunate to learn about this topic when I was developing MyBB way back years ago.  At the bottom of each MyBB page, administrators are shown debugging info with a summary of loading times and other statistics.

Debugging information presented to the administrator on each MyBB page

Clicking on the “advanced details” link yields a much more in-depth analysis of the page generation, including an explanation of all the database queries used to create the page.

Advanced performance debugging details presented to MyBB administrators.

This is very useful to determine where the bottlenecks occur when generating the page.  Some common mistakes include repeating similar queries instead of batching them together, selecting more data than is needed, and doing full table scans rather than indexed queries.

However, not all software and web frameworks has built-in performance debugging pages like what MyBB has.  If you’re programming something from scratch, it might take some extra effort to create a useful debugging interface.  What other tools can we use then?

Using the EXPLAIN query (works at least for MySQL, PostgreSQL, SQLite) will give you the details about how a specific query will be executed by the database.  All you need to do is to tack “EXPLAIN” on at the beginning of your SELECT query, and run it through your favourite database management program.  SitePoint has a thorough article on how to understand the output of an EXPLAIN query.

Of course, it’s also beneficial to have a concrete understanding of database indexes and how they work.  One of my favourite sites on this topic is Use the Index, Luke!  They also have a fun 3-minute quiz to test your knowledge, if you think you already know everything 😉  (I got 4/5 on my first try, how about you? 🙂 )

Do you know of any other resources to help with optimizing database queries?  Let me know in the comments.

MyBB: Progress Report

Well as you can see I haven’t been blogging about MyBB much.  In fact I haven’t been blogging that much at all, but that’s for a different blog entry.

I’ve been busy with school work and other stuff for the past several months, as I’m sure other people on the staff have been to.  But nonetheless we are pushing forward with MyBB 1.4 at a steady pace.

We have seen Chris blog about the new built-in search-engine-optimized URLs.  Although these “SEO” URLs are only present for pages with content that is “interesting” to search engine spiders (such as thread display, forum display, user profiles, announcements, and the calendar), they provide a much better “look” to the regular user than the standard ugly query string (for example, thread61881-5.html instead of showthread.php?tid=61881&page=5).

Then we have MyBB Merge (or is it MyBB Import, or MyMerge… ah well I don’t know exactly what name we have coined it today).  This idea of having a centralized import/merge system has been an idea of Chris Boulton for at least one or two years, but we did not have the time to really develop it.  But thanks to the work of Tikitiki (who is the lead of this project) users from a variety of other bulletin board systems can easily convert to MyBB.  But you ask, why does the name have “Merge” and not “Convert”?  Well because you can not only convert from another MyBB, but also merge the converted data to an existing MyBB forum if you wish.  Imagine what you can do with this system: You can have an existing MyBB, and merge another forum (from SMF, phpBB, vBulletin, Invision Power Board, and others, or even MyBB itself) into it.  Now how smart is that?  Of course if you don’t have an existing MyBB, you can just convert your forum, since you have nothing to merge it with.

Recently we ported MyBB Merge so that it is compatible with MyBB 1.2, and at the time of writing it is under beta testing.  Hopefully we can stamp out all the bugs so that we can release it to the general public.  We’re expecting that the release of MyBB Merge will bring a whole new community to MyBB who would not have considered converting because of a variety of reasons (converter does not exist, converter is broken, etc).