Apparently MySQL quietly (logical fallacy: I assume it was quiet because I didn’t hear anything about it) slipped a query profiler patch in MySQL 5.0 (as early as 5.0.37) that will give you time statistics about each step of a query’s lifetime. From the aforementioned:

mysql> show profile for query 1;
+--------------------+------------+
| Status             | Duration   |
+--------------------+------------+
| (initialization)   | 0.00006300 |
| Opening tables     | 0.00001400 |
| System lock        | 0.00000600 |
| Table lock         | 0.00001000 |
| init               | 0.00002200 |
| optimizing         | 0.00001100 |
| statistics         | 0.00009300 |
| preparing          | 0.00001700 |
| executing          | 0.00000700 |
| Sending data       | 0.00016800 |
| end                | 0.00000700 |
| query end          | 0.00000500 |
| freeing items      | 0.00001200 |
| closing tables     | 0.00000800 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)

This looks immensely useful! And while the MySQL documentation is mute on the matter, according to comments on the blog of the original author, the times reported aren’t specific to a thread. In other words, you’ll only get reliable numbers when there’s only one thread running.

Note: this seems to have been left out of the version of MySQL available in Gentoo portage. We’re not sure why, but we have some good minds (i.e., not mine) trying to figure out where it went. Consequently, I haven’t actually used it yet.

Apparently, in MySQL 5.0.36, there was a bug that prevented INSERT INTO ... ON DUPLICATE KEY UPDATE ... queries from flushing the query cache for the table they were modifying. According to the MySQL manual:

If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

But the evidence speaks for itself:

mysql> insert into sessions values ('fooh', 'blah', now())
  on duplicate key update session_data=values(session_data);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sessions where session_id='fooh';
+------------+--------------+---------------------+
| session_id | session_data | date_modified       |
+------------+--------------+---------------------+
| fooh       | blah         | 2007-07-27 07:41:57 | 
+------------+--------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into sessions values ('fooh', 'blah2', now())
  on duplicate key update session_data=values(session_data);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sessions where session_id='fooh';
+------------+--------------+---------------------+
| session_id | session_data | date_modified       |
+------------+--------------+---------------------+
| fooh       | blah         | 2007-07-27 07:41:57 | 
+------------+--------------+---------------------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from sessions where session_id='fooh';
+------------+--------------+---------------------+
| session_id | session_data | date_modified       |
+------------+--------------+---------------------+
| fooh       | blah2        | 2007-07-27 07:41:57 | 
+------------+--------------+---------------------+
1 row in set (0.00 sec)

Now, for us the fix is as simple as adding the SQL_NO_CACHE hint to our queries (or updating our version of MySQL, possibly). And, actually, adding it to the session queries isn’t a bad idea anyways — there’s not really any point in attempting to cache data from a table that gets written to with every page load. This could free up space in the query cache for other data that might have a chance of sticking.

As far as I can tell, this bug was fixed in 5.0.41 (at the latest).

There are a lot of instances where it’s extremely convenient to blobs of data in a MySQL database. It’s not necessarily the most efficient storage, but it definitely has convenience points.

Speaking of convenience, MySQL actually includes compression functions that you can use right in your queries, allowing you to store the data compressed, but sill have introspection abilities without any special interface. Unfortunately, however, those functions aren’t directly compatible with, say, the PHP gzip extension, so you’re stuck packing all of the compression load on your perhaps already overloaded database.

That is, until now. A few days ago I was facing this exact problem at work, and with a little goading from the DBAs (i.e., they said it was “impossible”), I determined that I’d fix it, regardless of what it took.

After a few skinny dips into both the MySQL and gzip-extension source code, I discovered that MySQL was prepending a long integer onto the beginning of the compressed string. Luckily, this is easy to replicate in PHP:

<?php
// compress compatible with MySQL UNCOMPRESS(...)
$compressed = pack('L', strlen($string)).gzcompress($string);

// uncompress compatible with MySQL COMPRESS(...)
$uncompressed = gzuncompress(substr($compressed, 4));
?>

Compression for all!