Using SQL Directives to Control Query Performance

Published November 9th, 2008

Problem Statement:

When dealing with performance issues, you need to have holistic view of the problem. You need to explore server configuration, database design/schema, query itself and programming approaches, infrastructure setup all together to make a combined effort to resolve it. Thus, it is hard to achieve quickly when you are already in the trenches. It is best to learn about performance and how to achieve them before you are in trouble. In such a spirit, lets review some interesting SQL directives that you can take advantage of to build better performing queries. We will use PHP examples here as PHP/MySQL is our bread and butter.

Delay that INSERT Query

Typically, a PHP programmer writes queries such as:

 // Old fashion
function insert()
{
   $stmt = "INSERT INTO $table (col1, col2, col3) values($col1, $col2, $col3)";
   // rest of the code is irrelevant or not shown for brevity
}

// Prepared statement
function insert()
{
   $stmt = "INSERT INTO $table (col1, col2, col3) values(? ? ?)";
   // rest of the code is irrelevant or not shown for brevity}
}

Here, we have created two version of a simple function called insert() that shows two common ways PHP develoeprs write INSERT queries.

// Old fashion
// We really wish you would use prepared statements - security and performance friendly
function insert($sqlDirective = null)
{
   $stmt = "INSERT $sqlDirective INTO $table (col1, col2, col3) values($col1, $col2, $col3)";
   // rest of the code is irrelevant or not shown for brevity
}

// Prepared statement (updated)
function insert($sqlDirective = null)
{
   $stmt = "INSERT $sqlDirective INTO $table (col1, col2, col3) values(? ? ?)";
   // rest of the code is irrelevant or not shown for brevity
}

Now when you know that you do not need to wait for insert to finish, in other words, you are not waiting for an auto increment field value or you are not relying on the new record in rest of the code for the “current execution” of your script, you can call:

// Tell MySQL that you do not need to wait for the query to complete so that
// it can finish it on its own
insert('DELAYED');

// Now call insert but do not delay it
insert();

When the INSERT query has the DELAYED directive, MySQL will leave the query with a thread and return execution back to your PHP script. This means you can continue doing something else or complete your script run faster.

Of course, you should use the DELAYED directive only where it is appropriate. For example, if you are creating a new user record and need the user ID that gets created by the auto increment field, you cannot use the DELAYED directive. On top of that, too much use of DELAYED might create a lot of threads that might eventually cause bigger performance issues. So use this directive wisely.

Slow Down that Update Query

Like INSERT DELAYED, it is possible to slow down your UPDATE statement using the LOW_PRIORITY directive. For example:

// Prepared statement
function update($sqlDirective = null)
{
   $stmt = "UPDATE $sqlDirective $table SET col1 = ? WHERE $whereClause";
   // rest of the code is irrelevant or not shown for brevity
}

// Call update and slow it down
update('LOW_PRIORITY');

// Call update and do not slow it down
update();

You should use the LOW_PRIORITY directive for update statements where you do not need the values back in your current execution of the script.

Slow Down Your DELETE Query

Similarly, you can use LOW_PRIORITY directive for DELETE queries.

Control Caching that SELECT Query

Often, when programmers learn about query performance problem, they go crazy with caching and turn on MySQL query caching. Unfortunately, MySQL query caching might have a complete reverse effect for situations where the table that MySQL is caching query for has frequent INSERT/UPDATE traffic. Whenever you cache query for a table that has a lot of say UPDATE traffic, the cache will become a bottlenack.

As UPDATE queries invalidate the cache, MySQL would be working harder to now recreate the cache and also performing your queries. So for frequently changing tables, DO NOT use MySQL query caching. Here is an example code:

// Select query
function select($sqlDirective = null)
{
   $stmt = "SELECT $sqlDirective FROM $table WHERE $whereClause";
   // rest of the code is irrelevant or not shown for brevity
}

// Call select but disallow caching
select('NO_SQL_CACHE');

// Call select and allow caching if caching is enabled
select();

When the select() is called with NO_SQL_CACHE directive, it tells MySQL not to cache the query. This is done knowing that the query in question is for a table that changes frequently or the query itself changes (say the where clause) or the query uses non-deterministic functions such as RAND(), COUNT(), etc. which provides different data when run.

Category: PHP, Performance, Programming | Tags: