Home arrow MySQL arrow Page 3 - Optimizing Queries with Operators for Date, Time and Other Functions

Other MySQL Functions - MySQL

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 third 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 for Date, Time and Other Functions
  2. Date and Time Conversion Functions
  3. Other MySQL Functions
  4. Branching: Making Choices in Queries
  5. Our Demonstration Revisited
By: Apress Publishing
Rating: starstarstarstarstar / 6
April 13, 2006

print this article



In this section, we’ll discuss a few miscellaneous functions that don’t fit in very well elsewhere, but which you may find useful in your quest to replace program logic with SQL logic for optimization purposes.

How Many Rows Actually Matched?

Sometimes when you’ve used a LIMIT clause in a SELECT query, it’s also handy to know how many rows would have been returned had the LIMIT not been used. In order to do this, first execute the query using the SQL_CALC_FOUND_ROWS option, followed by a SELECT FOUND_ROWS() query. The second query will return the number of all rows meeting the conditions set in the query, without the LIMIT clause.

For example, when displaying a heading such as “Displaying 1 through 10 of 22,052 records,” this approach is often faster and less cumbersome than writing a separate query using the COUNT() function.

IP Address Conversion

The INET_ATON() and INET_NTOA() functions convert an IP address string in dotted-quad format to an integer and back again.

Often, you need to keep IP addresses in logs in your applications. These two functions let you save a bit of space while doing so, since an IP address stored as a string requires at least 8 bytes of storage (and as many as 16 bytes), but when converted to an integer, it takes up 8 bytes and 8 bytes only.

What was the Last ID Inserted?

This LAST_INSERT_ID() function returns the last value generated for an AUTO_INCREMENT column for the current connection. It can be useful when inserting records into linked tables. For example, suppose that we’ve just accepted an order from a new customer, so that we need to insert a new record into a customers table and then a related record into an orders table. Assuming that the customerid column has the AUTO_INCREMENT modifier applied to it in the table definition, we could accomplish this like so:

INSERT INTO customers (customerid, lastname, firstname)
  VALUES ('', 'Smith', 'William');
INSERT INTO orders (orderid, customerid, orderdate)

The value returned by LAST_INSERT_ID() persists for the lifetime of the connection, so we could have done other things between the first INSERT query and the second without altering what was inserted into the customerid column of the new record in the orders table.

The value returned by LAST_INSERT_ID() is exclusive to each connection, and it’s not affected by inserts performed by other connections, even on the same tables. In other words, concurrency is never an issue when using this function.

User Variables

It’s a common programming practice to store values returned from queries in the application space for reuse. However, this can often be done within MySQL itself by means of user variables. A user variable (identified by a leading @ sign) can contain any scalar value and lasts for the lifetime of the current connection. It can be named using any combination of alphanumeric characters and may include the $ (dollar sign), _ (underscore), and . (period) characters.

You have two options for setting the value of a user variable:

  • The SET statement, which simply takes the form SET @varname = value;. value can be any legal MySQL expression that evaluates to a scalar value.
  • Within queries, you can use @varname:=value . In this case, the expression used for value can also include one or more column names.

Once the value of a user variable is set, you can use it in any expression. Here is an example that works in MySQL 4.1.1 and newer:

A user variable may hold any scalar type, including integers, floating-point numbers, strings, and dates. You can set a user variable’s value using an expression containing other user variables, as well as values derived from other functions.

  Never try to set a user variable and then use its value in a GROUP BY, ORDER BY, or HAVING clause in the same statement. The results of such a query are unpredictable.

Notice that you’re not limited (as is sometimes imagined) to setting user variables in SELECT queries, as you can see in the next example.

Unfortunately, in web applications, you cannot preserve values between pages by means of MySQL user variables, because each new page load creates its own connection. Even if you use persistent connections, you’re almost certain to get the value for a user variable of the same name that was set using a different connection (or a null value in the case where a new connection was established). MySQL won’t let you choose a connection identifier, nor can you create or set arbitrary global variables. Even so, user variables can still be useful for multiple queries within the same page and, as you’ll see in the example in the next section, for aliasing columns and intermediate results within queries where you can’t use real aliases.

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