MySQL seems to be very slow for updates-CodePudding

MySQL seems to be very slow for updates. A simple update statement is taking more time than MS SQL for same update call.

Ex:

UPDATE ValuesTbl SET value1 = @value1,
                     value2 = @value2
    WHERE co_id = @co_id
      AND sel_date = @sel_date

I have changed some config settings as below

innodb_flush_log_at_trx_commit=2

innodb_buffer_pool_size=10G

innodb_log_file_size=2G

log-bin="foo-bin"

skip-log-bin

This is the create table query

CREATE TABLE `valuestbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sel_date` datetime NOT NULL,
  `co_id` int(11) NOT NULL,
  `value1` decimal(10,2) NOT NULL,
  `value2` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21621889 DEFAULT CHARSET=latin1;

MySQL version: 8.0 on Windows

The update query takes longer time to update when compared to MS SQL, anything else I need to do to make it faster?

CodePudding user response：

There are no indices, the ValuesTbl tables has a PK, not using for anything. the id column is a Primary key from another table, the sel_date is a date field and 2 decimal columns

If there are no indexes on ValuesTbl then the update has to scan the entire table which will be slow if the table is large. No amount of server tuning will fix this.

A simple update statement is taking more time than MS SQL for same update call.

The MS SQL server probably has an index on either co_id or sel_date. Or it has fewer rows in the table.

You need to add indexes, like the index of a book, so the database doesn't have to search the whole table. At minimum an index on co_id will vastly help performance. If there are many columns with different sel_date per ID, a compound index on (co_id, sel_date) would help further.

See Use The Index, Luke for an extensive tutorial on indexes.