sorted by: recent | see : popular

Content Tagged with cache + Database

For years, MySQL never supported query cache with prepared statements. When I initially implemented MySQL prepared statements and its associated C API in MySQL 4.1 version; prepared statements were just saving the parsing cost of the query and nothing much. But now, the things are totally different.

Lot of developers stayed away from prepared statements due to this limitation (atleast for select statements). But now the latest MySQL 5.1 server does support the caching of prepared statements with some limitations.

I just wrote a simple C API test which uses mysql_prepare, mysql_execute and mysql_real_query and all seemed to work as expected and the cache works even with intermixed (prepare and non-prepare) on the same binary protocol. Here is a simple example how the QCache works with MySQL command line client when queries were intermixed with prepared and non-prepare execution on 5.1.23 server.

mysql> show status like '%cache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
+-------------------------+----------+
11 rows in set (0.01 sec)

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10),(20),(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

mysql> prepare stmt1 from 'select * from t1 where c1=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=10;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t1 where c1=10;
+------+
| c1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t1 where c1=20;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 1        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> set @a=20;
Query OK, 0 rows affected (0.02 sec)

mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 2        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t1 where c1=20;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 3        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t1 where c1=30;
+------+
| c1   |
+------+
|   30 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 3        |
| Qcache_inserts          | 3        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 3        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> set @a=30;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   30 |
+------+
1 row in set (0.00 sec)

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 4        |
| Qcache_inserts          | 3        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 3        |
+-------------------------+----------+
8 rows in set (0.00 sec)