A number of other keywords can be added to the SELECT statement to modify its behavior.
• The SQL_CACHE and SQL_NO_CACHE keywords tell MySQL whether the query results should be cached.
• The SQL_BUFFER_RESULT keyword forces MySQL to store query results in a temporary table. This result buffer eliminates the need for MySQL to lock the tables used by the query while the results are being transmitted to the client, thus ensuring they can be used by other processes in the interim.
• The SQL_BIG_RESULT and SQL_SMALL_RESULT keywords can be used to indicate the expected size of the result set to MySQL and, thereby, help it identify the most optimal way to sort and store the returned records (disk-based or inmemory temporary tables, respectively).
• The SQL_HIGH_PRIORITY keyword raises the priority of the query over competing UPDATE, INSERT, or DELETE statements, thereby resulting in (slightly) faster query execution on busy database servers.
• The SQL_CALC_FOUND_ROWS keyword tells MySQL to calculate the total number of rows matching the query, without taking into account any LIMIT that might have been set. This total number can then be retrieved via a call to the FOUND_ROWS()function.
Appropriate usage of the SQL_CACHE, SQL_BUFFER_RESULT, SQL_BIG_RESULT, SQL_SMALL_RESULT, and SQL_HIGH_PRIORITY keywords can significantly improve the speed of your transactions with the MySQL server.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
• The SQL_CACHE and SQL_NO_CACHE keywords tell MySQL whether the query results should be cached.
• The SQL_BUFFER_RESULT keyword forces MySQL to store query results in a temporary table. This result buffer eliminates the need for MySQL to lock the tables used by the query while the results are being transmitted to the client, thus ensuring they can be used by other processes in the interim.
• The SQL_BIG_RESULT and SQL_SMALL_RESULT keywords can be used to indicate the expected size of the result set to MySQL and, thereby, help it identify the most optimal way to sort and store the returned records (disk-based or inmemory temporary tables, respectively).
• The SQL_HIGH_PRIORITY keyword raises the priority of the query over competing UPDATE, INSERT, or DELETE statements, thereby resulting in (slightly) faster query execution on busy database servers.
• The SQL_CALC_FOUND_ROWS keyword tells MySQL to calculate the total number of rows matching the query, without taking into account any LIMIT that might have been set. This total number can then be retrieved via a call to the FOUND_ROWS()function.
Appropriate usage of the SQL_CACHE, SQL_BUFFER_RESULT, SQL_BIG_RESULT, SQL_SMALL_RESULT, and SQL_HIGH_PRIORITY keywords can significantly improve the speed of your transactions with the MySQL server.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
|
|
0 comments
Post a Comment