query_cache_type = 1
query_cache_size = 256M

#!/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()

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