March 20, 2008

MySQL Query Cache WhiteSpace and comments

Posted by peter

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:

PLAIN TEXT
SQL:
  1. mysql> SELECT /* my little comment */ count(*) FROM fact WHERE val LIKE "%c%";
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |        0 |
  6. +----------+
  7. 1 row IN SET (8.77 sec)
  8.  
  9. mysql> SELECT /* my little comment */ count(*) FROM fact WHERE val LIKE "%c%";
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. |        0 |
  14. +----------+
  15. 1 row IN SET (0.00 sec)
  16.  
  17. mysql> SELECT /* my little comment2 */ count(*) FROM fact WHERE val LIKE "%c%";
  18. +----------+
  19. | count(*) |
  20. +----------+
  21. |        0 |
  22. +----------+
  23. 1 row IN SET (0.00 sec)
  24.  
  25. mysql> SELECT /* my little comment4 */ count(*) FROM fact WHERE val LIKE "%c%";
  26. +----------+
  27. | count(*) |
  28. +----------+
  29. |        0 |
  30. +----------+
  31. 1 row IN SET (0.00 sec)
  32.  
  33. mysql> SELECT count(*) FROM fact WHERE val LIKE "%c%";
  34. +----------+
  35. | count(*) |
  36. +----------+
  37. |        0 |
  38. +----------+
  39. 1 row IN SET (8.79 sec)
  40.  
  41. mysql> SELECT count(*) FROM fact WHERE val LIKE "%c%";
  42. +----------+
  43. | count(*) |
  44. +----------+
  45. |        0 |
  46. +----------+
  47. 1 row IN SET (0.00 sec)
  48.  
  49. mysql>          SELECT count(*) FROM fact WHERE val LIKE "%c%";
  50. +----------+
  51. | count(*) |
  52. +----------+
  53. |        0 |
  54. +----------+
  55. 1 row IN SET (0.00 sec)
  56.  
  57. mysql> /* comment*/ SELECT count(*) FROM fact WHERE val LIKE "%c%";
  58. +----------+
  59. | count(*) |
  60. +----------+
  61. |        0 |
  62. +----------+
  63. 1 row IN SET (0.00 sec)
  64.  
  65. mysql> /* another comment */ SELECT count(*) FROM fact WHERE val LIKE "%c%";
  66. +----------+
  67. | count(*) |
  68. +----------+
  69. |        0 |
  70. +----------+
  71. 1 row IN SET (0.00 sec)
  72.  
  73. mysql> SELECT /* inside comment */ count(*) FROM fact WHERE val LIKE "%c%";
  74. +----------+
  75. | count(*) |
  76. +----------+
  77. |        0 |
  78. +----------+
  79. 1 row IN SET (8.81 sec)
  80.  
  81. mysql> SELECT /* inside comment */ count(*) FROM fact WHERE val LIKE "%c%";
  82. +----------+
  83. | count(*) |
  84. +----------+
  85. |        0 |
  86. +----------+
  87. 1 row IN SET (0.00 sec)
  88.  
  89. mysql> SELECT /* inside comment2 */ count(*) FROM fact WHERE val LIKE "%c%";
  90. +----------+
  91. | count(*) |
  92. +----------+
  93. |        0 |
  94. +----------+
  95. 1 row IN SET (0.00 sec)
  96.  
  97. mysql> SELECT /* inside comment4 */ count(*) FROM fact WHERE val LIKE "%c%";
  98. +----------+
  99. | count(*) |
  100. +----------+
  101. |        0 |
  102. +----------+
  103. 1 row IN SET (0.00 sec)
  104.  
  105. mysql> SELECT /* inside comment4 */count(*) FROM fact WHERE val LIKE "%c%";
  106. +----------+
  107. | count(*) |
  108. +----------+
  109. |        0 |
  110. +----------+
  111. 1 row IN SET (8.82 sec)

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:

PLAIN TEXT
SQL:
  1. | 798009298 | root        | localhost            | test           | Query          |       4 | Sending DATA                                                          | SELECT  count(*)   FROM fact WHERE val LIKE "%c%"

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.

Posted by peter @ 10:56 am :: production  
Print This Post Print This Post book mark MySQL Query Cache WhiteSpace and comments in del.icio.usdel.icio.us :: submit MySQL Query Cache WhiteSpace and comments to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :MySQL VIEW as performance troublemaker::How MySQL Query Cache works with Transactions::Beware large Query_Cache sizes:
 

5 Comments »

  1. 1. Justin Swanhart

    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.

    Comment :: March 20, 2008 @ 4:10 pm

  2. 2. peter

    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 :)

    Comment :: March 20, 2008 @ 8:55 pm

  3. 3. jenni s

    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;

    Comment :: March 25, 2008 @ 9:37 am

  4. 4. jenni s

    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.

    Comment :: March 25, 2008 @ 9:44 am

  5. 5. peter

    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

    Comment :: March 25, 2008 @ 11:44 am

 



Subscribe without commenting


This page was found by: mysql query comments query cache comments mysql query comment test mysql query cac... comments in mysql qu...