pythonmysqlsql-updatequery-optimizationbulkupdate

How can I speed up updating MySQL

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?

Solution

  • 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.