Using SELECT Statement Options

When we first looked at the SELECT statement, we looked at an abbreviated form of the general syntax for the statement. Let's come back and look at the complete syntax and see what we don't know.

According to the MySQL manual, this is the form of a SELECT statement:

SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows | rows OFFSET offset]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

Most of these clauses are now familiar to us. Let's briefly go through the ones we have not yet covered:

The STRAIGHT JOIN clause at the beginning can be used to force the query optimizer to join the tables in the order you specify. This has the same effect as specifying STRAIGHT JOIN in the WHERE clause, as discussed earlier in this chapter. This is an extension to ANSI SQL.

The SQL_SMALL_RESULT, SQL_BIG_RESULT, and SQL_BUFFER_RESULT options are designed to help with optimization. You can use SQL_SMALL_RESULT and SQL_BIG_RESULT to tell MySQL that you expect the result set to have either few rows or a large number of them. SQL_BUFFER_RESULT tells MySQL that it must put the result set into a temporary table. You can use this when it takes significant time to send the results to the client to avoid having the queried tables locked for that time. These options are MySQL extensions to ANSI SQL.

SQL_CACHE and SQL_NOCACHE tell MySQL whether to cache the results. (Another extension to ANSI SQL.)

SQL_CALC_FOUND_ROWS is for use with the LIMIT clause; it tells MySQL to work out how many rows would have been returned if there had been no LIMIT clause. We can then retrieve this number with select found_rows(); (another extension to ANSI SQL). This is intended to reduce duplicated effort. In versions without it, a common task is to run a COUNT(*) query and then a SELECT with a LIMIT.

HIGH PRIORITY tells MySQL that this query should be given priority over any UPDATE statements that are waiting to use the involved tables.

We have already talked about DISTINCT, but DISTINCTROW is a synonym for it. ALL is the opposite (return all duplicates) and is the default option.

The SELECT INTO OUTFILE is the opposite of the LOAD DATA INFILE command , "Inserting, Deleting, and Updating Data." This puts the result of the SELECT statement into the specified file. The export_options clause is the same as the options in LOAD DATA INFILE .

The PROCEDURE clause allows you to specify a procedure that can be applied to the result set before it is sent to the client. This procedure must be written in C++ and, as such, is beyond the scope of this book, but see the MySQL manual if you need more information.

The FOR UPDATE and LOCK IN SHARE MODE clauses affect you only if your storage engine uses page- or row-level locking—in practice, this is InnoDB and BDB. If you specify FOR UPDATE, you will set an exclusive lock, and if you use LOCK IN SHARE MODE, you will set a shared lock.