October 24, 2014

MySQL Query Cache WhiteSpace and comments

Commenting on my previous post on MySQL Query Cache Gerry pokes me as I’m all wrong and both comments and whitespace are fixed in MySQL 5.0. This was not what I remember seeing in production so I decided to do some tests on the matter:

I did the test two ways. First using command line client and second using little PHP script which just does the same query. I did this as command line client is known to optimize queries sometimes by skipping “unnecessary” comments.

So here is the command line run:

Looking at these results you could judge as all problems are indeed fixed. You can have whitespace in the start and you can have leading comment and it all works. However the comment which is inside the query works interesting way – the queries with different comments are both treated as same query if only comment is different. However if you change whitespace a bit (see the last query has space after comment deleted) it causes query cache miss.

If we look at process list output we can see the comments are actually skipped:

This tells us we should not use MySQL Command Line Client for any tests involving comments

Repeating queries from PHP instead we can learn the following about MySQL 5.0 Query Cache:

      Whitespace at the start of query does not block query from being cached. Moreover query with 2 spaces in front is considered same as query with 3 spaces in front
      Comment at the start of the query does not block query from being cached. However queries with different comments are considered different queries (it is not stripped before hashing) – so you should not put things like current time in such a comment.
      Comments inside the query also matter. Meaning if you place comments inside the query or in the end. Though this was always the case

So in the nutshell you should be more free now in regards of some SELECT queries not cached because of whitespace or comments in front of them. Though you still need to have queries exactly the same including comments to make them cached by query cache.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Justin Swanhart says:

    Is PHP stripping off the whitespace from the front of the query? I’m curious if your same test run under Perl::DBI would behave consistently with the PHP test.

  2. peter says:

    As far as I know PHP does not do any conversion to the query passed, so whitespace remains whitespace. Though you’re welcome to check it with DBI and let me know if it is different :)

  3. jenni s says:

    My understanding is that the mysql command line client now has an *option* to pass comments to the server, but it’s disabled by default:
    http://bugs.mysql.com/bug.php?id=26215

    I’m running the 5.0.56 client and server, and from the command line, my comment was stripped out (as seen by the query recorded in the slow query log). However, when I ran this php script:

    I *did* see the comment in the slow log:
    # Time: 080325 9:31:43
    # User@Host: dbadmin[dbadmin] @ localhost []
    # Query_time: 2 Lock_time: 0 Rows_sent: 230400 Rows_examined: 480
    select /* my comment */ * from test.WEBSITE a, test.WEBSITE b;

  4. jenni s says:

    it’s the same: the perl DBI doesn’t strip out whitespace or comments, but it will strip out whitespace at the beginning of comments.

  5. peter says:

    Thanks indeed there is an option to skip comments now and you should run mysql -c if you want do to this kind of resting with command line client

  6. Devis Lucato says:

    I’ve run a very simple test using with a PHP script and a table with ~400k rows.
    After restarting Mysql, to delete the cache, this is the result.

    Mysql : 5.0.51a-24 (Debian)
    PHP : 5.2.6-1+lenny2 with Suhosin-Patch 0.9.6.2 (cli) (built: Jan 26 2009 22:41:04)

    FIRST RUN

    /* comment 1 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.823220014572

    /* comment 2 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.852579116821 seconds

    /* comment 3 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.84706401825 seconds

    /* comment 4 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.83235502243 seconds

    /* comment 5 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.859950065613 seconds

    /* timestamp 1244198091 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.470226049423 seconds

    SECOND RUN

    /* comment 1 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.00132608413696

    /* comment 2 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.00090503692627 seconds

    /* comment 3 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.000951051712036 seconds

    /* comment 4 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.000913858413696 seconds

    /* comment 5 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.000999927520752 seconds

    /* timestamp 1244198149 */ SELECT count(*) FROM mytable WHERE myfield LIKE “%c%”
    0.39192199707 seconds

    I’d say that injecting dynamic comments clearly affects caching negatively, more memory consumption and slower results.

  7. peter says:

    Good catch.

    Hm…. Your results seems to show the comment upfront is not stripped – just does not block caching.
    All second query run queries but timestamp query were instant. Timestamp query was also the same query just with different leading comment and it was not cached.

    I’d consider it a bug.

  8. roselynn says:

    is there a way to include whitespace / space characters in a query?

    eg: ‘%to% go’

    will return: ‘to stay or go’
    but not: ‘tobago’

Speak Your Mind

*