How can I speed up updating MySQL

Question

How can I speed up updating data in my database? Do I need to change the loop or build the update in a different way?

try:
    g = 1
    for i in gate_io().values.tolist():
        with connection.cursor() as cursor:
            if i[1] != 0 and i[1] != '':
                insert_quarry = "UPDATE gate SET symbol = %s, bidPX = %s, askPx = %s WHERE id = %s"
                currency = [i[0], i[1], i[2]]
                cursor.execute(insert_quarry, (currency[0], currency[1], currency[2], g))
                connection.commit()
                g = g + 1
            else:
                continue
finally:
    connection.close()

Is it possible to use NumPy for this? Or are there other options?

score 3 · Accepted Answer · answered Oct 26 '22 at 18:04

3

Don't commit after every UPDATE. Instead commit after each batch of 100 or so. Most of the actual work of updating happens at commit.

Don't forget to commit the last batch.

answered Oct 26 '22 at 18:04

O. Jones

103,626
17
118
172

Each update still has a lot of overhead. You can do multiple rows in a single statement using a values table constructor – ysth Oct 26 '22 at 21:16
e.g. `update (select null new_id, null new_bar, new_baz where 0 union all values row(1,234,567),row(2,345,768)) newvalues join foo on id=new_id set bar=new_bar, baz=new_baz;` (omit "row" for mariadb) – ysth Oct 26 '22 at 22:40
it will be significantly faster even if you batch only 10 at a time (resulting query just has to be shorter than @@max_allowed_packet) – ysth Oct 26 '22 at 22:41
Query string can be set, once, out of loop. See also https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html#:~:text=executemany()%20Method,-Syntax%3A%20cursor.&text=This%20method%20prepares%20a%20database,found%20in%20the%20sequence%20seq_of_params%20.&text=In%20Python%2C%20a%20tuple%20containing,value%20must%20include%20a%20comma ( executemany method) – JL Peyret Oct 27 '22 at 00:30

How can I speed up updating MySQL

1 Answers1