Search code examples
mysqlperformancenode.jsnode-mysql

Speeding up the performance of huge multiple queries statements in MySQL

I have node.js script which is running as a cron job on the server.

It asyncronously receieves the data from external sources (several sumultaneous requests) and updates database with multiple queries statements where UPDATE queries are separated with semicolons.

One 8000-UPDATES multi-query runs about 55 seconds.

Is there the way to speed somehow up the total database updating process?

The single query is very simple - like 

 UPDATE my_table SET field1 = smth WHERE id = some_id;

The index is created for id field.

Solution

  • Reducing the number of queries performed is going to be your biggest win. Perhaps an INSERT...ON DUPLICATE KEY UPDATE?

    INSERT INTO my_table (id, field1) 
    VALUES
        (1, 'smth'),
        (10, 'smth2'),
        (88, 'smth3'),
        (23, 'smth4'),
        (68, 'smth5')
    ON DUPLICATE KEY UPDATE
        id = VALUES(id),
        field1 = VALUES(field1);