Editer le fichier my.cnf pour activer le cache des requetes:

query_cache_type = 1
query_cache_size = 256M

query_cache_type est le type de cache que l’on va adopter:
0 = pas de cache
1 = met en cache toutes les requetes sauf celles qui ont le flag “SELECT S_NO_CACHE”
2 = met en cache seulement les requetes qui comportent le flag “SELECT SQL_CACHE”

Ci dessous le script pour tester les perfs:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

from timeit import Timer 

def benchmark(cur):
    """
        execute query
    """
    cur.execute("SELECT * FROM Writers")
    rows = cur.fetchall()
    #     for row in rows:
    #         print row

def create_fixtures():
    """
        Create dummy datas for test
    """

    with con:
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Adolf Hitler')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Ronald McDonalds')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Mamie Nova')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Sam & Max')")

def set_query_cache(query_cache_type=1):
    """
        Set query cache
        0 : Don't cache results in or retrieve results from the query cache.
        1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
        2 : Cache results only for queries that begin with SELECT SQL_CACHE
    """

    with con:
        cur = con.cursor()
        cur.execute("SET GLOBAL query_cache_size = 16777216")
        cur.execute("SET SESSION query_cache_type = %s" % str(query_cache_type)) 

def start_benchmark(nb_queries=1000, cached_queries=1):
    """

    """
    # use cached query benchmark
    print "Starting benchmark: %s reads - query cache type = %s" % (nb_queries, cached_queries)

    # set query cache
    set_query_cache(cached_queries)

    # run the test
    t = Timer("queries()", "from __main__ import queries")
    print t.timeit(number=nb_queries) 

if __name__ == '__main__':

    # connect to db
    con = mdb.connect('localhost', 'root', '12345', 'test');

    # create dummy datas
    create_fixtures()

    with con:
        cur = con.cursor()
        queries = lambda: benchmark(cur=cur)

    # launch benchmark
    start_benchmark(nb_queries=10000, cached_queries=1)
    start_benchmark(nb_queries=10000, cached_queries=0)

    con.close()

Ce qui me donne…

python mysql_tests.py
Starting benchmark: 10000 reads - query cache type = 1
1.47591710091
Starting benchmark: 10000 reads - query cache type = 0
1.96538686752

Conclusion:
Il semblerait qu’en effet le gain dû au cache est plutôt pas mal, en plus c’est juste 2 params à mettre dans son fichier de config.

Faites part de vos retours, ça peut toujours servir ;)