Make 10k queries SQL faster

Question

I'm trying to perform over than 10k queries to mysql server from Python and here is my prepared query statement

UPDATE `rates` 
SET `in`=%s, `out`=%s, `amount`=%s, `minamount`=%s, `maxamount`=%s 
WHERE `from`='EXMRUB' AND `to`='YAMRUB' AND `id_site`= 1

The problem is that it runs really slow, I mean it takes about a 5 minute to perform 10k updates, how can I modify, my query to make it faster?

You may try adding an index on the combination of the three columns which appear in the `WHERE` clause. There is no guarantee that MySQL would use this index though while updating. — Tim Biegeleisen, Jan 02 '20 at 15:19

score 1 · Accepted Answer · answered Jan 02 '20 at 15:44

Assuming that id_site is your table's primary key, you could use a little MySQL trick in combination with executemany to speed up the update:

INSERT INTO rates (id_site, `in`, `out`, amount, minamount, maxamount)
    VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
    `in`=VALUES(`in`)
,   `out`=VALUES(`out`)
,   amount=VALUES(amount)
,   minamount=VALUES(minamount)
,   maxamount=VALUES(maxamount);

executemany is very important, because it makes sure that the loop is not done on the client side.

score 0 · Answer 2 · answered Jan 02 '20 at 15:20

0

You can speed up each individual query with an index on (``from``, ``to``, ``id_site``).

However, you are probably better off loading the data into a separate table in the database and then doing the update in a single statement, using join.

answered Jan 02 '20 at 15:20

Gordon Linoff

1,242,037
58
646
786

I really don't understand JOIN's and INNER's so it would be really great if you show a little example with my query – DispooL Jan 02 '20 at 15:22
8

Also: Please _don't_ name your columns using keyword like `FROM` and `TO` – Tim Biegeleisen Jan 02 '20 at 15:23

Make 10k queries SQL faster

2 Answers2