MySQL Optimization Hints

databases

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's builtin query caching mechanism for a particular query. You can help MySQL make the query cache more efficent by using this hint on queries that are highly dynamic (such as a keyword search, or a report that only runs nightly). Make sure 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 query cache also covers this hint.

HIGH_PRIORITY

The HIGH_PRIORITY hint can be used on SELECT or INSERT statements to let MySQL know that this is a high priority query. This hint will basically allow the query to skip in line.

LOW_PRIORITY

The LOW_PRIORITY hint can be used on INSERT and UPDATE statements. If you use the LOW_PRIORITY keyword, execution of the query is delayed until no other clients are reading from the table. This means that you may wait a LONG time, or forever on servers with a heavy read volume.

INSERT DELAYED

An INSERT LOW_PRIORITY statment will not return until the statement has been executed, which could possibly be forever. Instead you can use an INSERT DELAYED statement. It will return immediately, but it will still wait until other clients have closed the table before executing the statement.

I have written an article on 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 MySQL, and I can also recommend the book High Performance MySQL by Jeremy Zawodny and Derek Balling from O'Reilly.



Related Entries

19 people found this page useful, what do you think?

Trackbacks

Trackback Address: 613/8EBB7F63F04B4E1657C66C9241AE2CA5

Comments

On 01/03/2007 at 6:18:41 AM UTC Tim B wrote:
1
Thanks for those tips

On 06/19/2007 at 2:48:39 PM UTC Fred Fillon wrote:
2
Hi There,

I release a Php Script for optimization and mysql Tuning.

You can view main variables / status about mysql server, and overall you can adjust settings on fly.

A running demo : http://www.fillon.org/mysysop

Download available http://www.fillon.org/

On 06/17/2008 at 1:15:55 PM UTC Matthew Montgomery wrote:
3
Original bash script from which most mysysop advisors are derived. Tuning Primer http://forge.mysql.com/projects/project.php?id=44

On 03/28/2009 at 2:50:39 AM UTC vkpal wrote:
4
I found a lot php mysql query http://www.muchads.com forums

On 07/20/2010 at 12:29:58 PM UTC Mark wrote:
5
Yes, We'll use fot mysql databases of our site http://www.decasasyautos.com Thanks.

Mark DeCasasyAutos.com

On 02/18/2012 at 10:39:01 AM UTC Jorge wrote:
6
An article about MySQL optimization in http://investigacionit.com.ar/2012/02/optimizacion-de-bases-de-datos-mysql/ Optimización de MySQL

On 02/22/2012 at 12:12:26 PM UTC jorge wrote:
7
I wrote an article about MySQL Optimization. I hope it helps someone: http://investigacionit.com.ar/optimizacion-de-bases-de-datos-mysql/

On 09/05/2014 at 5:48:50 PM UTC salvatore ferragamo wrote:
8
http://www.salvatoreferragamooutletsale.com ferragamo outlet salvatore ferragamo http://www.salvatoreferragamooutletsale.com

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?