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

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. [FONT='Courier New', Courier, monospace]| 798009298 | root | localhost | test | Query | 4 | Sending DATA | SELECT count(*) FROM fact WHERE val LIKE "%c%" [/font]

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:

• 1. 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
  1. 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.
  1. 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.
Entry posted by peter | No comment
Add to: | | | |


MySQL Query Cache WhiteSpace and comments - Read More...