HandlerSocket execute_multi Curiosities
A post on the HandlerSocket-dev mailing list the other day got me thinking about the performance of MySQL’s IN() construct versus HandlerSocket’s execute_multi. So I started a little test, using MySQL 5.5 + HandlerSocket’s latest commits:
mysql> CREATE TABLE `test`.`t1` (
-> `id` int unsigned NOT NULL AUTO_INCREMENT,
-> `val` char(32) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `test`.`t1` VALUES (1,'one'),(2,'two'),(3,'three');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
I then captured the output of SHOW GLOBAL STATUS and executed the following query:
mysql> SELECT `id`,`val` FROM `test`.`t1` WHERE `id` IN (1,2,3);
I then immediately captured the output of SHOW GLOBAL STATUS again. Next, I performed the HandlerSocket equivalent (using Perl’s Net::HandlerSocket module):
my $rarr = $hs->execute_multi([
[ 0, '=', [ '1' ], 1, 0 ],
[ 0, '=', [ '2' ], 1, 0 ],
[ 0, '=', [ '3' ], 1, 0 ],
]);
Next, I again captured the SHOW GLOBAL STATUS output. Theoretically the only differences in relative changes between MySQL’s IN() and HandlerSocket’s execute_multi would be:
Hs_table_open Hs_table_close Hs_table_lock Hs_table_unlock
However, what I found was quite surprising. Handler_read_key increased more for access via HandlerSocket. I spot checked with several queries and this was consistently the case. Anybody know off the top of their head the answer to this puzzle?
