Optimizing Queries with Operators for Date, Time and Other Functions - Other MySQL Functions
(Page 3 of 5 )
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)
VALUES ('', LAST_INSERT_ID(), CURRENT_DATE);
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.
TIP 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.
CAUTION 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.
Next: Branching: Making Choices in Queries >>
More MySQL Articles
More By Apress Publishing
|
 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 
