Jul 1

Performance Tuning with mySQL Keywords

mySQL

Ever have performance issues with your queries even after you’ve optimized them? You
can do a few mySQL specific tricks using keywords in order to force certain behaviors.

These are a couple of my commonly used mySQL-specific keywords to performance-tune.

HIGH_PRIORITY
This makes your query jump to the head of the queue, use it for queries that have a
higher priority, but use it sparingly. In all honesty, I think I MIGHT have used this
one time.

LOW_PRIORITY
Only for use on SELECT and UPDATE queries, this allows you to mark a query as
a query that isn’t time-sensitive. Pretty much all of my scheduled updates and
most of my “invisible” updates/inserts use this so that user-facing queries are
more responsive.

SQL_BIG_RESULT
Use this for complex queries with large result sets, especially those that aren’t user facing. In a nutshell it tells mySQL there will be a lot of results and to go ahead and create a temporary table on the disk instead of in memory.

SQL_BUFFER_RESULT
This will put the query’s results into a temporary table. Basically, you can use it
to avoid table locking problems. I don’t use this much since I don’t work with many
big result sets except in a few rare cases. Most of the time I end up choosing SQL_BIG_RESULT.

SQL_NO_CACHE
If you have query caching turned on, using this keyword will ensure that the results
of the query aren’t cached. Use it for queries that can be expected to have different
results every time it is run.

SQL_CACHE
This is the other side of the SQL_NO_CACHE coin, use this to force a query to be
cached if you’re running in explicit mode (mode 2).