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 we looked at in Chapter 5, "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 (see Chapter 5 for details).

  • 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. We will discuss locking in Chapter 10, "Using Transactions with InnoDB Tables."



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net