Home arrow MySQL arrow Page 2 - Optimizing Queries with Operators, Branching and Functions

Optimization 1: Separation of Logic and Formatting - MySQL

This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the first of three parts, and 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).

  1. Optimizing Queries with Operators, Branching and Functions
  2. Optimization 1: Separation of Logic and Formatting
  3. MySQL Operators
  4. Type Conversions with Logical and Arithmetic Operators
  5. Operators for Working with Sets
  6. Type Conversions in Comparisons
By: Apress Publishing
Rating: starstarstarstarstar / 13
March 30, 2006

print this article



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.

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
result = cursor.fetchall()
#  begin HTML output
print "Content-Type: text/html"
print "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \ 

           \"http://www.w3.org/TR/html4/ loose.dtd\">\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:

  1. Send the query.
  2. Receive the result.
  3. 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

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.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: