 MySQL Page 2 - Optimizing Queries with Operators, Branching and Functions |
Optimization 1: Separation of Logic and Formatting - MySQLThis 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).
print this article SEARCH DEV SHED |
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.
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 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:
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.
blog comments powered by Disqus |
/ 13 
Using Subqueries In MySQL (part 2)
