Optimizing Queries with Operators, Branching and Functions, continued

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 second 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).

TABLE OF CONTENTS:

By: Apress Publishing

Poor Best

Rating:

/ 6

April 06, 2006

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

MySQL Functions

We are now going to look at a number of MySQL functions. Again, the focus here is on the central premise of this chapter: replacing programming logic with SQL logic is a key optimization skill, and it is almost always the best course of action to make the database perform as much work as possible. You want to minimize the amount of filtering and manipulating data that you do in your programming code.

Of course, MySQL has quite a large number of built-in functions, and some of them have a great many possible arguments as well. We won’t attempt to catalog all of them here—that’s what the MySQL Manual is for, after all—but we will discuss and demonstrate those functions that, in our experience, have proved to be the most important and useful in terms of replacing programming logic with SQL logic.

Math Functions

MySQL has most of the standard mathematical functions you would expect to find in any programming language. These include rounding functions, exponential and logarithmic functions, and even trigonometric functions.

Rounding Functions

All of the following functions round a floating-point number to one with fewer digits to the right of the decimal point or to an integer in one way or another:

CEILING(): Returns the smallest integer that is not less than the argument. CEILING(2.45) returns 3; CEILING(-2.45) returns –2. It can also be written as CEIL() in MySQL 4.0.6 and later.

FLOOR(): Returns the largest integer that’s smaller than the argument. FLOOR(2.65) yields 2; FLOOR(-2.65) returns –3.

TRUNCATE (N, D): Returns N truncated to D decimal places. TRUNCATE (2.45, 1) returns 2.4; TRUNCATE(-2.45, 1) yields –2.4. It is possible for D to be larger than the number of decimals in N; for example, TRUNCATE(2.45, 3) returns 2.450. Note that both arguments are required; TRUNCATE(2.45, 0) evaluates to 2. By using a negative value for D, you can round to the left of the decimal point as well; TRUNCATE(542.5, -1) yields 540 and TRUNCATE(542.5, -2) returns 500.

ROUND(N[, D]): Rounds N to D decimal places; if D isn’t specified, then 0 is implied. Note that the manner in which rounding is performed is system-dependent. If portability is an issue for your application, or if you need to be able to depend on the exact behavior, consider using TRUNCATE(), FLOOR() , or a user-defined function (UDF) instead. Beginning in MySQL 5.0, you can also define a stored procedure or function that implements the exact rounding algorithm desired. (See Chapter 8 for information about user-defined functions and stored procedures.)

TIP MySQL also has a convenient function for formatting numbers for display: FORMAT(N, D) returns a number N formatted in comma-separated format truncated to D decimal places. For example, FORMAT(23456.789, 2) returns the string '23,456.78' .
Powers, Exponents, and Logarithms

To obtain powers of numbers in MySQL, you can use the POW() or POWER() function (the two function names are synonymous). For example, 10 to the power of 2 (that is, 10 * 10 or 10²) can be found using POW() as shown here:

The logarithm of X to the base Y is defined as the power to which Y must be raised is order to produce X. For example, the logarithm of 100 to the base 10 is 2, because 10 to the power of 2 is 100. Using mathematical notation, we would write this relationship as this:

log₁₀100 = 2 • 10²= 100

The most frequently used base for logarithms in mathematics, physics, and engineering is the special value e, where e is calculated by this series:

Logarithms to the base e are known as natural logarithms (loge, often written as ln) and can be obtained in MySQL using the LOG() function. Enterprise applications don’t have that many uses for natural logarithms. However, it can sometimes be quite handy to be able to calculate base-2, base-8, base-10, or base-16 logarithms, which you can do by making use of the fact that logxy = ln y / ln x. Here is an example, in which the second result tells us that we need six digits to express the base 10 number 100 in binary notation.

NOTE The logarithm of 1 to any base is always 0; the logarithm of any number less than or equal to 0 (to any base) is always undefined and in MySQL will return NULL. Attempting to obtain a logarithm to the base 1 or 0 will also yield a NULL result.

In MySQL 4.0.3, logarithmic functions were improved in a number of ways. The function LN() was added as another means for calculating natural logarithms, and the LOG() function was enhanced so that it became possible to find logarithms bases other than 2 without needing to perform division using the notation LOG(base, value). Here are a couple examples:

When used with a single argument, LOG() still returns the natural logarithm. The LN() function may take only a single argument and always return the natural logarithm.

TIP To learn more about natural logarithms and e,a good place to start is on the MathWorld web site at
http://mathworld.wolfram.com/e.html.

The functions LOG2() (logarithm to the base 2) and LOG10() (logarithm to the base 10) were also added in MySQL 4.0.3.

To get the value of a number raised to the power of e, use the EXP() function, as shown in the next example.