Wednesday, January 07, 2009

Optimizer tricks

When you write good SQL, that use indexes properly there is one more obstacle that can slow down your app. The mySQL optimizer. From versions 3.23 to 5.1 the optimizer has been a problem for me. In mySQL 6.0 SUN/mySQL has resources improving it.

I wrote a post detailing how to pick indexes to get the most out of mySQL here.

Here is a post about the mySQL optimizer and what you can do to speed up your SQL SELECT statements.

What I would like to share with you today, is that UPDATE and DELETE statements can also use optimizer tricks that SELECT uses. Its not documented on the mysql.com but it is possible to do something like


UPDATE [YOUR TABLE] USE INDEX(`your_index_name`) SET col='val' WHERE [columns that satisfy your index].


To see if you have problems with your UPDATE statements taking to much time I recommend 1st running INNODB as your storage engine since SHOW INNODB STATUS will indicate what index your long running UPDATE is using. This can be achieved by looking for /RECORD LOCKS space id.* index/ of SHOW INNODB STATUS.

Another indicator that you might need to force the optimizer to do the right thing is to track "Deadlock detected" from concurrent update statements; look at the where clause of your UPDATE statement.

Next solve your issues by telling mySQL to use the correct index when setting exclusive locks via the USE INDEX statement above.

Hope this helps.

1 comment:

Shlomi Noach said...

Hi,

Good notion.
However, changing your storage engine to see what kind of indexes may sometimes not do what you would want.

Since InnoDB uses clustered index (as opposed to MyISAM, Falcon, Maria), all secondary indexes implicitly contain the primary key.

This means that when using InnoDB you can get a different execution plan than than the one you would get using MyISAM.
Please see a couple of posts I wrote.

Regards,
Shlomi Noach