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.

Debug info
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.

Performance debugging details
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: Reasons Against a Database Import Feature

Ever since MyBB had a facility to create database backups, we have had questions on why there wasn’t a similar facility to import backups.  The following reasons were taken into account while designing this feature:

1. Security

Reason

If a user somehow managed to get into an administrative account with the correct permissions, they could potentially revert your forum to an earlier state, or run arbitrary SQL queries on your database.

The Alternative

The use of an externally-installed database administration application reduces the chances that a malicious user can perform these actions on your database through MyBB.

2. Focus

Reason

We are developing a bulletin board system. We are focused on bringing you the features that will help run your bulletin board. Although database backup and restore are important tools, they are not used on a day-to-day basis in your forum’s administration. The backup function has more frequent use than the restore anyway. Our development resources are limited and we do not have the capacity to develop a fully-featured database administration program into the bulletin board software.

The Alternative

Third-party database administration programs are specifically developed for the purpose of managing your database. Their features are more robust than what we can provide. Just like how we concentrate on discussion board features, their development teams concentrate on what their product is: database administration.

Weight

Reason

In order to keep the MyBB package a reasonable size, we have to be conscious of what features are implemented. It is a waste of both physical and human resources to have a large package (clunky to install and update) with a steep learning curve (difficulty of usage).

The Alternative

Dedicated database administration solutions have the ability to incorporate more useful administrative features because that is their focus.

Reliability

Reason

In the hypothetical case where an import facility is indeed incorporated into MyBB, the requirement of using it is a working version of MyBB. Let’s say you just upgraded to MyBB 1.4, but you do not like it. Your database backup is 30MB. You start restoring it via the MyBB 1.4 Admin CP, but the restore times out part way. Your database is now half MyBB 1.2.x and half MyBB 1.4.x and neither version will operate correctly. You will need to resort to a third-party database administration program anyway to complete the process of reverting. A doctor can’t heal himself if he is unconscious.

The Alternative

A third-party database administration program does not depend on anything you are operating on. Even if an operation times out, this does not affect the program, you will be able to use the same program to correct the problem.

I hope this sheds some light on the rationale behind not including a database import feature.

MyBB 1.4 Released

We released MyBB 1.4 to the masses this weekend.  I wouldn’t say it’s “overdue” but it’s about time we had this release out the door.  We still haven’t managed to get the “self-fix” module working for MyBB to fix itself, so as usual support threads are pouring into the MyBB Community Forums.  Unfortunately I was caught up in school and work to do as much development as I wanted to for 1.4, but the other developers did a great job recoding the entire Admin CP, and all the other things.

Hope you enjoy MyBB 1.4.

Removing the MyBB Copyright Illegally is a Bad Idea

All administrators of MyBB probably know of the MyBB License one way or another. However, it has come to my attention that there are some people who just can’t get their head around one statement in the license. It happens to be this one:

The MyBB Group has several copyright notices and “powered by” lines embedded within the product. You must not remove, alter or hinder the visibility of any of these statements (including but not limited to the copyright notice at the top of files and the copyright/powered by lines found in publicly visible “templates”).

Is the text too difficult to understand? Personally I think we have made this as clear and as inambiguous as possible. Would you not agree? To me, it is quite easy to understand from this quotation that I am not allowed to remove any copyright notice and “powered by” statements which have been already written into the files distributed in the MyBB package. I don’t see any other way of interpreting it.

Is the license too difficult to find? The license is distributed in the Documentation folder of the full MyBB package. It is also displayed when the forum is installed. By installing a forum, the administrator has to at least see one, if not both instances where the license is displayed during the installation of a forum.

Are you that ashamed of running MyBB? I have seen MyBB forums changing the “powered by” line to other software such as vBulletin. I mean, if you like vBulletin, or IPB, or the other software that much, why not use that instead? Why mask MyBB as another software? Is there something wrong with using MyBB? If so, I’d suggest you voice your feedback on the MyBB Community Forums. We respect all feedback, and we take in your suggestions when we’re developing the next version of MyBB. We want to make MyBB something that you and your users want to use. This isn’t just for our own benefit.

I spent an hour tonight surfing the web looking for violators, and with a simple web search, I was able to find around 40 within the time I spent. And that was only for one search. I’m sure there are hundreds of forums out there who have removed the MyBB copyright and/or “powered by” lines.

To the MyBB forum owners who have removed the copyright and/or “powered by” lines, it isn’t a matter of if your forum will be found, it’s when. We enforce our License to the fullest extent possible, including legal action if required. How would it feel if you spent hours working on something to have it taken by another person who claims it is theirs? Not too happy I’d assume. We aren’t either.

Support us, and we will support you. It’s only two lines with links to the official MyBB homepage at the bottom of your forum. Is that too much to ask really?

If you really despise the copyright lines that much, please consider purchasing the privilege of removing the copyright on one board with a small payment to Chris Boulton. Please contact him for more details about this.

PS: Thanks to everybody (the majority of MyBB administrators) who is adhering to the License and thus supporting us 🙂

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).