public
Created

Performance test for MySQL UPDATE

  • Download Gist
db_test.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
# -*- coding: utf-8 -*-
"""
Performance test for different types of update
 
 
 
Results sample
-----------------------------------------------
 
In [1]: import db_test
 
In [2]: db_test.prepare()
 
In [3]: %timeit db_test.insert_on_duplicate_key_update()
10 loops, best of 3: 61 ms per loop
 
In [4]: db_test.prepare()
 
In [5]: %timeit db_test.update()
10 loops, best of 3: 52.1 ms per loop
 
In [6]: db_test.prepare()
 
In [7]: %timeit db_test.update_case()
10 loops, best of 3: 33.3 ms per loop
 
"""
import random
import MySQLdb
 
db = MySQLdb.connect(host='localhost', db='db_test', read_default_file='~/.my.cnf')
db.autocommit(False)
 
nrows = 100
nrows_total = 100000
 
 
def prepare():
"""
Prepare mysql table "foo"
"""
c = db.cursor()
c.execute("""
DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rating` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE InnoDB DEFAULT CHARSET latin1;
""")
c.close()
c = db.cursor()
rating_values = [(1, ) for _ in xrange(nrows_total)]
c.executemany("INSERT INTO `foo` (`rating`) VALUES (%s)", rating_values)
c.close()
db.commit()
 
 
def insert_on_duplicate_key_update():
"""
Update multiple rows at once with "INSERT .. ON DUPLICATE" statement
"""
ids = random.sample(xrange(1, nrows_total + 1), nrows)
rating_values = [(id, 2) for id in ids]
c = db.cursor()
c.executemany('INSERT INTO `foo` (`id`, `rating`) VALUES (%s, %s) '
'ON DUPLICATE KEY UPDATE `rating` = VALUES(`rating`)',
rating_values)
c.close()
db.commit()
 
 
def update():
"""
Update multiple rows in transaction
"""
ids = random.sample(xrange(1, nrows_total + 1), nrows)
c = db.cursor()
for id in ids:
c.execute('UPDATE `foo` SET `rating` = %s WHERE `id` = %s', [2, id])
c.close()
db.commit()
 
 
def update_case():
"""
Update multiple rows with case / when / then
"""
ids = random.sample(xrange(1, nrows_total + 1), nrows)
values = [2 for _ in xrange(nrows)]
c = db.cursor()
when_statement = ' '.join('WHEN %s THEN %s' % args for args in zip(ids, values))
in_statement = ','.join(str(i) for i in ids)
 
c.execute('''UPDATE `foo` SET `rating` = CASE `id`
%s ELSE `rating` END
WHERE `id` IN (%s)''' % (when_statement, in_statement))
c.close()
db.commit()

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.