Le coût des tables temporaires en SQL

Lorsque certaines requêtes SQL ont de trop gros résultats, dépassant la taille de tmp_table_size, MySQL ouvre automatiquement une table temporaire sur le disque pour y stocker les données. C'est transparent pour l'utilisateur, hormis pour les performances : une table sur le disque est beaucoup plus lente qu'en mémoire.

Peter Zaitsev compare alors une table temporaire sur le disque et en mémoire : la différence est un gain de l'ordre de 100 fois.

Pour tirer partie des tables temporaires en mémoire, il faut régler deux variables : max_heap_table_size, qui indique la quantité de mémoire réservée aux opérations en mémoire pour MySQL, et tmp_table_size, qui mesure la taille maximale d'une table temporaire en mémoire avant de la passer sur le disque.

Au passage, deux trucs intéressants à noter :
SELECT * FROM table GROUP BY col ORDER BY NULL;
Group by a la facheuse habitude de trier les résultats : avec order by NULL, on peut forcer GROUP BY a ne pas trier, ce qui fait gagner beaucoup de temps selon Peter.

Quand on modifie une variable globale, la connexion courante n'est pas affectée. Seule les nouvelles connexions sont affectées...

•  How much overhead is caused by on disk temporary tables (42 visites)