June 16, 2011, 1:49 p.m.
posted by dver
Combine Your Queries
Not only can you combine related queries, but you also can merge unrelated queries. If these queries are always used together anyway, combining them can result in a measurable performance improvement.
The time it takes for your program to send a query to the database, and for the database to send the results back to your program, is a significant factor in making your database-driven web sites seem quick and responsive. When you have optimized your queries to filter in the database system [Hack #8], you may discover that both the database and the program seem to spend significant periods of time waiting on each other to respond. This is particularly true if your programs and database server run on different machines.
One of the best ways to minimize round-trip time for multiple queries is to stop using multiple queries and to use only a single query. You will save yourself the round-trip network time for all the separate queries you didn't use, plus a good deal of CPU time by not executing the code to build, send, run, receive, and decode the query information for the queries that were avoided.
The tricky part about combining queries is that your program will eventually have to split the single result set back into separate rows from each combined query involved. The queries involved may have different numbers of columns with different types.
Consider a typical web-driven site, with page content in one table (shown in Figure) and a message of the day in another table (see Figure). A CGI program makes queries to load the page needed (in this case, index.html) and to load the message of the day (motd), and then combines them to form a pretty page layout. It still needs two queries, which add up to twice the round-trip time required for one query.
A typical approach might be:
SELECT pagename,content FROM page WHERE pagename = 'index.html'
SELECT message FROM motd
You could instead combine them, with sets of columns for each different query, and UNION it all together by using NULLs where necessary:
SELECT pagename,content,NULL,'page' FROM page WHERE pagename = 'index.html' UNION SELECT NULL,NULL,message,'motd' FROM motd
Your client program can split them up again by using column 4 to indicate the source table (motd or page). Of course, the query is also really difficult to read, but it may be worth it if you need the speed or if you run these queries often.