MyEye update

Since ages, I was busy with many projects including my marriage. Nice to be back on the blog with an update on MyEye - the project I announced in my very first post. MyEye is an (or is going to be an) open source monitoring tool for MySQL installations with handy knowledge-base and advisories for MySQL DBAs. So, I'm done with the first round of analysis and design for MyEye. As far as it is planned, it's going to be a framework that would provide interface for people to write their own monitoring rules. Meanwhile MyEye web site is also under construction and I will be publishing the documentation soon there. Will be looking forward to your contributions for creating an open knowledge base for MyEye.

Customized Order By Sequence - A Small Hack

What if you have to customize the sequence of order by clause on a column. Say, you have a integer column 'weight' that can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the field function.

   1: mysql> SELECT * FROM table ORDER BY FIELD(weight, 4, 2, 5, 1, 3);

Now what does it do. Field function returns the index of the first argument in the rest of the list. So, effectively the order by logic will be the same, just that the values that are being ordered are generated on the fly.

Other approach can be to use CASE.. WHEN .. END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:

   1: mysql> SELECT *, CASE 

   2:     -> WHEN weight = 1 THEN 4

   3:     -> WHEN weight = 2 THEN 2

   4:     -> WHEN weight = 3 THEN 5

   5:     -> WHEN weight = 4 THEN 1

   6:     -> WHEN weight = 5 THEN 3

   7:     -> END AS weight_custom

   8:     -> FROM table

   9:     -> ORDER BY weight_custom;

But I'm a little biased towards the first approach due to its simplicity. Though it's a matter of personal choice.

Such requirements are rare, just a food for thought.

Hope this blog was helpful to you. Keep posting your comments.

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