Optimizing Queries with Operators, Branching and Functions - Optimization 1: Separation of Logic and Formatting
(Page 2 of 6 )
First, we can separate the logic required for formatting the data that’s retrieved from the display loop. This is much cleaner code and makes it easier to modify either the data being passed to the display loop (by modifying the query) or the display loop itself.
For example, if we decide to output the names as lastname, firstname, rather than firstname lastname, we simply change the relevant part of the query to CONCAT(lastname, ', ', firstname) AS name , without needing to touch the PHP code. If we do need to alter the presentation, we can tweak the display code without fear of messing up the derivation of the data or formatting of the data itself.
As an added bonus, you’ll often find that your code is a bit shorter as well, because you can write fewer and/or shorter loops.
Optimization 2: Speed
This method is also faster. If you’re retrieving only a few records, the difference in speed between these two PHP scripts might not be very noticeable, but increase that number to, say, 50,000 records, and you’ll see that the second ver sion executes about 10% to 15% more quickly than the first. Naturally, any improvements you might see in your own applications from adopting this methodology are going to be affected by a wide range of variables, but in an enterprise setting with vast amounts of data and many users, or on a busy web site with hundreds or even thousands of simultaneous visitors, any performance gain you can muster becomes important.
TIP You can often pick up an additional speed boost by making use of the query-caching capabilities available in MySQL 4.0 and later. See Chapter 6 for a discussion of query caching.
Optimization 3: Portability
Finally, this method is also more portable. Suppose we’re told that (a) we need to port this script to (just for the sake of example) Python and (b) this really needed to be done yesterday. Which version of the PHP script would you prefer to work from—particularly if you’re not a Python expert?
Working from the second version of the PHP script plus a rudimentary knowledge of Python, a copy of the Python documentation, and the MySQLdb module ( http://sourceforge.net/projects/mysql-python ), Jon was able to produce a working script in about 30 minutes (including the time required to download, install, and configure ActivePython and MySQLdb ):
#!/usr/bin/pytho n
import cgi
import MySQLdb
# connect to MySQL and create a cursor
db = MySQLdb.connect(host="localhost", user="zontar", passwd="mypass", db="mdbd") cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
# the same query used in the "improved" PHP 4 example
query = "SELECT \
CONCAT(firstname, ' ', lastname) AS name, \
CASE \
WHEN (@age:=YEAR(CURRENT_DATE) - YEAR(dob)) > 65 THEN 'Over 65' \
WHEN @age >= 45 THEN '45-64' \
WHEN @age >= 30 THEN '30-44' \
WHEN @age >= 18 THEN '18-29' \
ELSE 'Under 18' \
END AS age_range \
FROM members \
ORDER BY lastname, firstname"
# submit the query and store the result set
cursor.execute(query)
result = cursor.fetchall()
# begin HTML output
print "Content-Type: text/html"
print
print "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \
\"http://www.w3.org/TR/html4/ loose.dtd\">\n\
<html>\n<head>\n\
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">\n\
<title>List All Members With Age Ranges [Python-CGI / Logic in SQL]</title>\n\ </head>\n<body>"
# begin HTML table display
print "<table cellpadding=\"5\" cellspacing=\"0\" border=\"1\">\n\
<tr><th colspan=\"2\">MEMBERS BY AGE GROUP</th></tr>\n\
<tr><th>Name</th><th>Age Group</th></tr>"
# display result rows in HTML table rows for row in result:
print "<tr><td>%s</td><td>%s</td></tr>" % (row[0], row[2])
# end table display
print "</table>"
# end HTML page
print "</body>n\</html>"
# end of script
The query does all the hard work of calculation, decision-making, string manipulation, and so forth. This means that the tasks remaining for the script in Python (or whatever language the boss told us to use) are easy to implement, almost trivially so:
- Send the query.
- Receive the result.
- Generate the dynamic output by looping through the result set.
Notice that no heavy-duty parsing, calculation, or string-manipulation code is required; we’ve already done all that using nothing but SQL. In other words, we’ve abstracted all of those tasks out of our display code and into the query.
Figure 4-1 shows the output of the Python script in a web browser.
Figure 4-1. Output of the demonstration example
NOTE If you haven’t encountered Python before and are interested in checking it out, you can obtain the latest version (Python 2.3.4 as of this writing) for a variety of platforms from either http://www.python.org/ (Unix source code;Windows and Mac OS X binaries) or http://activestate.com/Products/ ActivePython/
(installers for Windows, Linux, and Solaris). You might also want to pick up a copy of Magnus Lie Hetlend’s Practical Python (Apress, 2002), which we’ve read and can highly recommend.
Next: MySQL Operators >>
More MySQL Articles
More By Apress Publishing
|
 This article is excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324). Check it out at your favorite bookstore today. Buy this book now.
|
|
/ 11 
