Défaillance du Query Cache de MySQL

Il arrive que le Query Cache ne fonctionne pas :-(

J'ai par exemple était confronté à un problème concernant le Query Cache lorsque j'ai voulu optimiser certaines requêtes effectuées sur un serveur. En fait, l'optimiseur de MySQL, en version 4.1.11, ne faisait pas le bon choix d'index selon certains cas, et scinder la requête en 2 sous requêtes unifiées ensuite permettait d'obtenir de meilleurs performances. Attention, ce n'est pas toujours le cas !
En exécutant plusieurs fois les 2 requêtes unitairement le Query Cache fonctionnait correctement comme vous pouvez le voir au temps d'exécution qui était > 2 s.

mysql> SELECT idancetre,max(idmess) mxd FROM messages USE INDEX (idx_tst3) WHERE idancetre not in (1021576,0) AND avalider=0 AND idmess>1021576 GROUP BY idancetre ORDER BY mxd DESC LIMIT 5;
+-----------+---------+
| idancetre | mxd |
+-----------+---------+
| 1993983 | 1995071 |
| 1994149 | 1995069 |
| 1995061 | 1995061 |
| 1994957 | 1995059 |
| 1994250 | 1995058 |
+-----------+---------+
5 rows in set (0.00 sec)


mysql> SELECT idmess,idmess FROM messages WHERE idancetre=0 AND avalider=0 AND idmess>1021576 GROUP BY idmess ORDER BY idmess DESC LIMIT 5;
+---------+---------+
| idmess | idmess |
+---------+---------+
| 1169825 | 1169825 |
| 1169813 | 1169813 |
| 1169795 | 1169795 |
| 1169758 | 1169758 |
| 1169756 | 1169756 |
+---------+---------+
5 rows in set (0.00 sec)

Par contre, la requête UNION composée des 2 requêtes précédentes n'était jamais stockée dans le Query Cache :

mysql> (SELECT idancetre,max(idmess) mxd FROM messages USE INDEX (idx_tst3) WHERE idancetre not in (1021576,0) AND avalider=0 AND idmess>1021576 GROUP BY idancetre ORDER BY mxd DESC LIMIT 5) UNION (SELECT idmess,idmess FROM messages WHERE idancetre=0 AND avalider=0 AND idmess>1021576 GROUP BY idmess ORDER BY idmess DESC LIMIT 5);
+-----------+---------+
| idancetre | mxd |
+-----------+---------+
| 1993983 | 1995071 |
| 1994149 | 1995069 |
| 1995061 | 1995061 |
| 1994957 | 1995059 |
| 1994250 | 1995058 |
| 1169825 | 1169825 |
| 1169813 | 1169813 |
| 1169795 | 1169795 |
| 1169758 | 1169758 |
| 1169756 | 1169756 |
+-----------+---------+
10 rows in set (2.55 sec)

Vous le comprendrez bien, dans ce cas là, il est préférable d'exécuter les 2 requêtes de façon distincte et d'effectuer l'UNION côté applicatif plutôt qu'en une seule et même requête. En fait, dans cette version c'était dû à un BUG référencé chez MySQL et corrigé en 4.1.17, 5.0.17 et 5.1.4

SELECT queries that began with an opening parenthesis were not being placed in the query cache. (Bug#14652)

En 5.0.67, qui contient le bugfix, ce n'est pas la même chose :

mysql> (SELECT idancetre,max(idmess) mxd FROM messages USE INDEX (idx_tst3) WHERE idancetre not in (1021576,0) AND avalider=0 AND idmess>1021576 GROUP BY idancetre ORDER BY mxd DESC LIMIT 5) UNION ALL (SELECT idmess,idmess FROM messages WHERE idancetre=0 AND avalider=0 AND idmess>1021576 GROUP BY idmess ORDER BY idmess DESC LIMIT 5);
+-----------+---------+
| idancetre | mxd |
+-----------+---------+
| 1993983 | 1995071 |
| 1994149 | 1995069 |
| 1995061 | 1995061 |
| 1994957 | 1995059 |
| 1994250 | 1995058 |
| 1169825 | 1169825 |
| 1169813 | 1169813 |
| 1169795 | 1169795 |
| 1169758 | 1169758 |
| 1169756 | 1169756 |
+-----------+---------+
10 rows in set (0.00 sec)

Il est donc très important de vérifier les changement apportés par les versions mineures qui suivent la version que vous utilisez pour savoir quels bugs ont été corrigés, et lesquels peuvent vous concerner.

Blogged with the Flock Browser