Infobright バージョン

$ mysql-ib
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.1.40 build number (revision)=IB_4.0.7_r16961_17249(ice) (static)

テーブル定義

mysql> show create table t2;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `key1` char(50) DEFAULT NULL,
  `key2` char(50) DEFAULT NULL,
  `key3` char(50) DEFAULT NULL,
  `key4` char(50) DEFAULT NULL,
  `data1` char(50) DEFAULT NULL,
  `data2` int(11) DEFAULT NULL,
  `data3` int(11) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 |

一部クエリーの見直し

data2 の大小比較前に key1,2,3 の条件をチェック

select
    key4,data1,data2,data3
from
    t1
where
    key1='aaa'
and
    key2='bbb'
and
    key3='ccc'
and
    data2 >= n1 and data2 < n2
and
    key4 in (
        'ddd'
       ,'eee'
        ・・・
    )
order by
    key4,data1,data2
;

結果

処理時間 (秒)

key4 数 (データ取得数)	10 (14400)	20 (28800)	30 (43200)
in 指定	1.898	3.290	4.679
in 指定 (前回InfiniDB)	0.190	0.218	0.273

(ちなみに)圧縮状態はこんな感じ

mysql> show table status;
+------+-------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| Name | Engine      | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment                                              |
+------+-------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------------------------+
| t2   | BRIGHTHOUSE |      10 | Compressed | 5754240 |              1 |     8301443 |               0 |            0 |         0 |           NULL | 2015-07-04 10:44:48 | 2015-07-04 10:45:33 | NULL       | latin1_swedish_ci |     NULL |                | Overall compression ratio: 179.442, Raw size=1420 MB |
+------+-------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------------------------+

mysql> show full columns from t2;
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+--------------------------------+
| Field | Type     | Collation         | Null | Key | Default | Extra | Privileges                      | Comment                        |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+--------------------------------+
| key1  | char(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 0.1; Ratio: 999.99   |
| key2  | char(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 0.1; Ratio: 999.99   |
| key3  | char(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 1.6; Ratio: 171.27   |
| key4  | char(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 4.3; Ratio: 63.71    |
| data1 | char(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 1.9; Ratio: 141.05   |
| data2 | int(11)  | NULL              | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 0.1; Ratio: 999.99   |
| data3 | int(11)  | NULL              | YES  |     | NULL    |       | select,insert,update,references | Size[MB]: 0.1; Ratio: 999.99   |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+--------------------------------+

この compression ratio はとても魅力的！！

気付き

　？？？

　極端ではないにしても、遅い・・・

　高圧縮ゆえのことなのか、テーブル構成とクエリーの書き方のバランスが取れていないのか。

　圧縮率が非常に魅力的なだけに、もうちょっと調べてみようと思う。

リンク

クエリーで or と in の処理時間差を確認する
 クエリーで or と in の処理時間差を確認する (その3)