Our Demonstration Revisited - MySQL

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.