Every programmer loves to optimize, even when we know query caching is turned on otherwise there is no need for this command.
Checkout my article on the MySQL Query Cache for more info.
SQL_CACHE
If you have setup MySQL Query Caching to explicit mode (set query_cache_type = 2) then you can use the SQL_CACHE hint to tell MySQL which queries to cache.
My article on the MySQL Insert Delayed as well.
Note: INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables.
STRAIGHT_JOIN
This hint will tell MySQL to join the tables in the order that they are specified in the FROM clause.
Use EXPLAIN to make sure that MySQL has not already figured out the optimal join order. And if you specify an ill order you can make MySQL do a lot more work than it needs to.
SQL_BUFFER_RESULT
This hint tells MySQL to put the result of the query into a temporary table. This will free up a table lock while the resultset is being sent to the client. So you would only want to use this on large result sets.
SQL_BIG_RESULT
The SQL_BIG_RESULT hint can be used with DISTINCT and GROUP BY SELECT statements. It as you might guess, tells MySQL that the result set will be big. According to the MySQL documentation, if invoked
MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements.SQL_SMALL_RESULT AS you might guess this is pretty much the opposite of
SQL_BIG_RESULT. When enabled MySQL uses fast temporary tables to store the resulting table instead of using sorting. Since this is typically the default route of the optimizer, this hint is often not needed.
If you want to dig deeper, have lots of articles on High Performance MySQL by Jeremy Zawodny and Derek Balling from O'Reilly.
Related Entries
Premature optimization is the root of all evil - December 8, 2005
The MySQL Query Cache - June 21, 2005
Comments
http://www.petefreitag.com/) is a software engineer, and
web developer located in central new york. Pete specializes in the
HTTP protocol, web services, xml, java, and coldfusion. In 2003 Pete
published the ColdFusion MX Developers Cookbook with SAMs Publishing.
Pete owns a Firm called Foundeo (
Suggest a Correction
Found an error or have a suggestion? Let us know and we'll review it.
No comments yet. Be the first to comment!