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.

Mac OS X Terminal startup slow?

Tonight, I was finally fed up with my Terminal window taking nearly 10 seconds to show the initial prompt.  I regularly use Terminal to SSH into other computers and to commit and pull code changes in git repositories. The delay was annoying so I looked for help.

Mac OS X Terminal Window
I almost always finish typing my first command before the initial prompt appears.

Luckily, other people on the Internet have noticed the same issue so it wasn’t hard to find a solution. The first one I came across was on this blog post on OSXDaily. Initially, I was a little skeptical of log files having anything to do with the Terminal startup time, but other Google results also came up with the same solution.

The solution is to remove the *.asl (Apple System Log) files from the /private/var/log/asl/ folder. The Terminal command to remove those log files is:

sudo rm -rf /private/var/log/asl/*.asl

This is obviously a short term solution, as new log files will presumably accumulate over time again. I found additional information on SuperUser and ProposedSolution that you can check out, if you are looking for a deeper explanation and/or a long-term solution.