TokuDBで変にパーティションが遅い件の比較用XtraDB

これもメモ調で。
昨日の TokuDBでパーティションが変に遅い件 との比較。

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (num)
PARTITIONS 10 */
1 row in set (0.05 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (num)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (600000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (700000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (800000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (900000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

昨日のテーブルをデータ入ったままALTER TABLE .. Engine= InnoDBしただけ。

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.46 sec)

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT COUNT(*) FROM t3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.49 sec)

同じクエリーを4回投げて一番遅いのを捨てて中央値を取るのはいっしょ。
プルーニングが効かない全スキャンなのでt1が一番速いとはいえ、やっぱりこんなもんだよね。

mysql> SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

プルーニング効かないはずのt2のが速い。。

mysql> explain partitions SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 214438 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.03 sec)

mysql>
mysql> explain partitions SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions                    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t2    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | range | PRIMARY       | PRIMARY | 4       | NULL | 201950 | Using where; Using index |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t3    | p9,px      | range | PRIMARY       | PRIMARY | 4       | NULL | 49983 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

XtraDBはちゃんとプルーニング効きつつtype: rangeになった。