Home arrow MySQL arrow Optimizing Queries with Operators, Branching and Functions, continued

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

  1. Optimizing Queries with Operators, Branching and Functions, continued
  2. Trigonometric Functions
  3. Other Math Functions
  4. Conversion of Numbers Between Bases
  5. String Functions and Regular Expressions
  6. Pattern-Matching and Regular Expressions
By: Apress Publishing
Rating: starstarstarstarstar / 6
April 06, 2006

print this article



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

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 102) 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:

log10100 = 2 ē 102 = 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.

 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(basevalue). 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


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.


 POW(), EXP(), and LOG() always return decimal values, even if all arguments are integers.


 POW(), EXP(), and LOG()

>>> 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: