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