InnoDB Row Counting using Indexes

This is always mentioned that InnoDB is slower in giving results for COUNT(*) as compared to MyISAM. But as Peter points out in his blog that this fact only applies to COUNT(*) queries without WHERE clause. This text is from Peter's blog only - "If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions." Let's see what EXPLAIN has in store for us.

   1: mysql> CREATE TABLE `test_index` (

   2:   `id` int(11) NOT NULL AUTO_INCREMENT,

   3:   `x` int(11) DEFAULT NULL,

   4:   `y` int(11) DEFAULT NULL,

   5:   `z` varchar(255) NOT NULL DEFAULT 'testing',

   6:   PRIMARY KEY (`id`),

   7:   KEY `x` (`x`,`y`)

   8: ) ENGINE=InnoDB;

   9:  

  10: mysql> EXPLAIN SELECT COUNT(*) FROM test_index \G

  11: *************************** 1. row ***************************

  12:            id: 1

  13:   select_type: SIMPLE

  14:         table: test_index

  15:          type: index

  16: possible_keys: NULL

  17:           key: PRIMARY

  18:       key_len: 4

  19:           ref: NULL

  20:          rows: 4875772

  21:         Extra: Using index

  22: 1 row in set (0.01 sec)

  23:  

  24: mysql> SELECT COUNT(*) FROM test_index;

  25: +----------+

  26: | COUNT(*) |

  27: +----------+

  28: |  4915200 |

  29: +----------+

  30: 1 row in set (2.61 sec)

The explain states that the counting is going to be done on PRIMARY index and Using Index. The best part is that since it is going to use PRIMARY index and since it is NOT NULL, MySQL will actually count the values from the index itself. So, contrary to the thought that something like COUNT(1) will work faster is not true in this case. Here is an interesting case from a bug.

   1: mysql> CREATE TABLE `test` (

   2:   `id` int(11) NOT NULL,

   3:   `int_k` int(11) NOT NULL,

   4:   `data1` varchar(255) NOT NULL,

   5:   `data2` varchar(255) NOT NULL,

   6:   PRIMARY KEY (`id`),

   7:   KEY `int_k` (`int_k`)

   8: ) ENGINE=InnoDB DEFAULT CHARSET=latin1

   9: 1 row in set (0.00 sec)

  10:  

  11: mysql> CREATE PROCEDURE populate()

  12: begin

  13:   declare i int;

  14:   set i = 0;

  15:   start transaction;

  16:   while i < 300000 do

  17:     insert into test (id, int_k, data1, data2)

  18:       values (i, i, repeat("-", 250), repeat("-", 250));

  19:     set i = i + 1;

  20:     if i % 1000 = 0 then

  21:       start transaction;

  22:     end if;

  23:   end while;

  24:   commit;

  25: end

  26: 1 row in set (0.00 sec)

  27:  

  28: mysql> call populate;

  29: Query OK, 0 rows affected (1 min 0.65 sec)

  30:  

  31: mysql> SELECT COUNT(*) FROM test;

  32: +----------+

  33: | COUNT(*) |

  34: +----------+

  35: |   300000 |

  36: +----------+

  37: 1 row in set (1.08 sec)

  38:  

  39: mysql> SELECT COUNT(*) FROM test use index (int_k);

  40: +----------+

  41: | COUNT(*) |

  42: +----------+

  43: |   300000 |

  44: +----------+

  45: 1 row in set (0.12 sec)

Using a secondary index is faster. But why? Generally speaking, the PRIMARY index should be faster because it is usually in order and can be read with sequential I/O at around 15 times more speed than generally fragmented secondary index. Actually this is a special case, since the secondary index is inserted into the table in perfect order, which is very rare. Also, as Heikki points out in the bug

"Since the minimum record size of InnoDB is about 20 bytes, and the fill-factor of a secondary index is typically 70 %, we can calculate that if the row length is > 15 * 1.5 * 20 = 450 bytes, then scanning the secondary index would probably be a better option." Till this feature is implemented and we have a much better optimized count(*) for InnoDB, please use secondary index explicitly for counting rows if you satisfy any of above conditions. Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: InnoDB, MySQL, Performance, Indexes