Mysql DML Performance

Question

Hi

In my case I have a Mysql Table with 2,976,977 records. This table has indexes on two columns, and I used these two columns while updating and inserting records from multiple clients. The problem is that it is taking too much time for updating or inserting the values. Is any Idea to speed up this?

Answer 1

Indexes may not make inserting and updating faster - they could make them slower because the indexes need to be updated.

Answer 2

From the comments, i get the impression it is mainly doing the 20000 update statments that is taking too long. Are you running the updates in autocommit mode (default)? if so, then you can improve performance a lot by doing:

START TRANSACTION;
...update statements go here...
COMMIT;

please note that if one of the updates does not succeed due to a constraint violation, you cannot COMMIT. And your data will not be stored permanently until you do COMMIT.

In java, you could do something like:

java.sql.Connection conn = DriverManager.getConnection(connectString, userName, passWord);
//explicit transaction by disabling autocommit.
conn.setAutocommmit(false);
java.sql.Statement stmt = conn.createStatement();

//add all your statements to the batch, f.e. in a loop
for (...) {
    stmt.addBatch(sql);
}

int results[] = stmt.executeBatch();
...check the results here...

//store results permanently in db
conn.commit();