Optimizing Queries with Operators for Date, Time and Other Functions - Our Demonstration Revisited
(Page 5 of 5 )
Let’s look again at the query we used at the beginning of this chapter:
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;
While it may look complicated, it actually just returns two columns, aliased as name and age_range. The first of these is relatively simple: we simply
concatenate the firstname and lastname columns with a space in between and return the result as name. This is obviously more efficient because we return just one column instead of two.
The age_range column appears a bit more complex. Let’s look at the first WHEN clause by itself:
WHEN (@age := YEAR(CURRENT_DATE) - YEAR(dob)) > 65 THEN 'Over 65'
Here’s the “trick” that we alluded to earlier: Since we’re not interested in the actual age of each member, but only in the age category to which he or she belongs, we set a user variable @age in the first WHEN clause, and then use that value in each of the succeeding ones. Since we’re not trying to use this value in a GROUP BY, ORDER BY, or HAVING clause, we can do this; the value will remain constant (unless we explicitly set it to another value). Notice that we place the expression in which the value of @age is to be set (the difference in years between the year of birth and the current one) inside parentheses. Otherwise, @age would be set to the value of the expression YEAR(CURRENT_DATE) – YEAR(dob) > 65 ; that is, either 1 or 0 (TRUE or FALSE), and so the column value would always be returned as either 'Over 65' or 'Under 18'.
As for the APIs we used for accessing MySQL from PHP and Python scripts, we’ll cover those in the next chapter.
Summary
In this chapter, we basically gave you a crash course in the most common and useful MySQL operators and built-in functions, all the time keeping in mind our central premise of giving you as many opportunities to consider replacing programming logic with SQL logic for optimization purposes.
Why should you go to the trouble of learning and using all of these constructs? The question contains its own answer: so that you can minimize having to do so in your application logic. This makes database interaction faster and more efficient because you generally need to return less data from the database, and because (particularly in the case of web applications) MySQL can usually manipulate the data faster than your programming code can. This is due to the speed at which set processing (operating on a group of records as a single entity) operates as opposed to row processing (working on each record as a separate iteration of a loop).
Maximizing the work done by MySQL also makes migration of your MySQL applications between programming languages and platforms much easier. This may seem trivially self-evident, but as we showed in our demonstration example in this chapter, if your queries already produce the data in the form required for output by your application, your application only needs to output it.
What’s Next
Now we’ve covered just about everything that you need to know in order to create well-structured tables, put data into them, and get that data back out again. In Chapter 5, we’ll examine some additional MySQL features that you can employ in your applications. The first of these is the join, which is simply a query that selects data from multiple tables. This is obviously more efficient than querying one table, and then using the results to provide parameters for making queries on others. There are several different join types in SQL (and thus MySQL); we’ll look at each of these and provide some examples highlighting their use and the differences between them.
Another helpful MySQL feature is the temporary table, which can be used to store results of queries and calculations for further use in a manner that’s independent of your application. Temporary tables are also convenient in that they only last for the duration of a single session, so there’s usually no need to worry about cleanup after you’re finished with them.
Both of these features can help you to save time and effort when developing applications. They’ll also prove invaluable in getting more work out of MySQL (instead of in your application code) and making more efficient use of MySQL itself.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 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.
|
|
/ 6 
