MySQL update query extremely slow with indexes

Question

This query takes a lot of time to execute:

UPDATE facturacion2013_full a, facturacion2013_contadores b SET a.B1 = b.consumo WHERE a.contador = b.contador

Here my table structure:

CREATE TABLE IF NOT EXISTS `facturacion2013_full` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poliza` int(8) NOT NULL,
  `contador` varchar(12) NOT NULL,
  `B1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fields_idx` (`contador`),
  KEY `fields_iidx2` (`poliza`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;

CREATE TABLE IF NOT EXISTS `facturacion2013_contadores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poliza` int(8) NOT NULL,
  `contador` varchar(12) NOT NULL,
  `consumo` int(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`contador`),
  KEY `idx2` (`poliza`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;

I've tried changing little things with no luck. Also tried to change some parameters in the mysql config like buffer size, max_heap_table_size... What am I missing? Thanks in advance!

Answer 1

I tested this on MySQL 5.6, which does support EXPLAIN for UPDATE statements.

mysql> explain UPDATE facturacion2013_full a JOIN facturacion2013_contadores b 
    ON a.contador = b.contador 
    SET a.B1 = b.consumo\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: fields_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx1
          key: idx1
      key_len: 14
          ref: test.a.contador
         rows: 1
        Extra: NULL

You can see that it reads ALL rows of table a. I'm assuming from the table names that there are more rows in a than rows in b. So it reads all rows from a whether there are matching rows in b or not.

Perhaps it would save MySQL some work to reorder the tables so that it reads ALL the rows of the smaller table, and only matching rows in the larger table.

mysql> explain UPDATE facturacion2013_contadores b JOIN facturacion2013_full a
    ON a.contador = b.contador
    SET a.B1 = b.consumo\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: idx1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: fields_idx
          key: fields_idx
      key_len: 14
          ref: test.b.contador
         rows: 1
        Extra: NULL

The order of tables shouldn't matter, because the optimizer should be able to reorder tables by itself, reading them in a better order if necessary. But sometimes the optimizer isn't as smart as we would like. Or it may even reorder the tables in a way that makes the query worse. You're using MySQL 5.1, which is pretty old (its supported lifespan ended 12/31/2013 in fact), so perhaps the optimizer wasn't so good as newer versions.

You can override the optimizer and force the tables to be read in the order you give them in the query, by using the STRAIGHT_JOIN keyword:

mysql> explain UPDATE facturacion2013_contadores b STRAIGHT_JOIN facturacion2013_full a
    ON a.contador = b.contador
    SET a.B1 = b.consumo\G

---

For what it's worth, you can also test the optimization of UPDATE statements on MySQL 5.1. It's pretty simple to rewrite any UPDATE statement as an equivalent SELECT, for purposes of seeing how the optimizer will analyze it. Just move the SET expressions into the select-list.

mysql> explain SELECT a.B1 = b.consumo FROM facturacion2013_full a 
    JOIN facturacion2013_contadores b ON a.contador = b.contador\G