Robin Schumacher

I posted a note last week about the new beta of MySQL 5.1 being released (5.1.17). One thing that we believe needs a special call out is the fact that prepared statements can now work with the query cache. Observe:

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.17-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.02 sec)

mysql> show global status like '%qc%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8379648 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 0       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> prepare tst from "select broker_last_name from broker where office_location_id > 1";
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql> execute tst;
+------------------+
| broker_last_name |
+------------------+
| MCMILLIAN        |
| MONSOUR          |
| FRAZIER          |
| HIGHT            |
| KAELIN           |
| MCMANUS          |
| PERKINS          |
| SANDERS          |
| SCHWARTZ         |
| SCHULTZ          |
| JACKSON          |
| FORD             |
| CARRIER          |
| BONNER           |
| BOYCE            |
| LEE              |
| BRADY            |
| HARBSMEIR        |
+------------------+
18 rows in set (0.00 sec)

mysql> execute tst;
+------------------+
| broker_last_name |
+------------------+
| MCMILLIAN        |
| MONSOUR          |
| FRAZIER          |
| HIGHT            |
| KAELIN           |
| MCMANUS          |
| PERKINS          |
| SANDERS          |
| SCHWARTZ         |
| SCHULTZ          |
| JACKSON          |
| FORD             |
| CARRIER          |
| BONNER           |
| BOYCE            |
| LEE              |
| BRADY            |
| HARBSMEIR        |
+------------------+
18 rows in set (0.00 sec)

mysql> show global status like '%qc%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8378112 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 0       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Very nice! Of course, be sure to check the 5.1 manual for the restrictions that do apply. Enjoy!

This entry was posted on Monday, April 16th, 2007 at 10:37 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.