Dear all,
I would like to receive your thoughts, comments on the implementation I am working on.
I am using infobright with mondrian for OLAP analysis.
I was wondering if I should use the classical aproach of a star schema (ie 1 fact table and differents dimensions tables) or a unique flat table with degenerated dimensions inside.
I have chosen the second one, a unique flat table.
I have about 50 columns (varchar, int and decimal) and 90’000’000 rows.
I was expecting some better performance on simple queries generated by mondrian. For instance,
mysql> select `fact_5`.`some_name` as `c0`, `fact_5`.`some_num` as `c1` from `fact_5` as `fact_5` group by `c0`, `c1` order by `fact_5`.`some_name` ASC;
+------+------+
| c0 | c1 |
+------+------+
| NULL | NULL |
| 1 | 1 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 19 | 19 |
| 2 | 2 |
| 20 | 20 |
| 21 | 21 |
| 22 | 22 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| X | 23 |
| XY | 25 |
| Y | 24 |
+------+------+
26 rows in set (13.05 sec)
mysql> select `fact_5`.`beset_type_name` as `c0`, `fact_5`.`beset_name` as `c1`, `fact_5`.`some_name` as `c2`, `fact_5`.`some_num` as `c3` from `fact_5` as `fact_5` where (`fact_5`.`some_name` = '1') group by `c0`, `c1`, `c2`, `c3` order by `fact_5`.`beset_type_name` ASC, `fact_5`.`beset_name` ASC, `fact_5`.`some_name` ASC;
..................
| SRP | rz9988443 | 1 | 1 |
| SRP | rz9988572 | 1 | 1 |
| SRP | rz9988575 | 1 | 1 |
| SRP | rz999095 | 1 | 1 |
| SRP | rz999915 | 1 | 1 |
+------+--------------+------+------+
27127 rows in set (44.73 sec)
I don’t know if theses execution time are normal or if they should be better.
Also, here is an extract of the ddl :
CREATE TABLE `POC_DW`.`fact_5` (
`beset_name` varchar(64) DEFAULT NULL COMMENT 'lookup',
`beset_type_name` varchar(64) DEFAULT NULL COMMENT 'lookup',
`some_name` varchar(8) DEFAULT NULL COMMENT 'lookup',
`some_num` int(11) DEFAULT NULL,
...............
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1
and some additionnal information :
+--------+-------------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------------+
| 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 |
+--------+-------------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------------+
| fact_5 | BRIGHTHOUSE | 10 | Compressed | 88942765 | 13 | 1179572176 | 0 | 0 | 0 | NULL | 2010-08-30 04:40:26 | 2010-08-30 04:40:21 | NULL | latin1_swedish_ci | NULL | | Overall compression ratio: 26.282 |
+--------+-------------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------------+
1 row in set (0.00 sec)
+----------------------------------------------+---------------+-------------------+------+-----+---------+-------+---------------------------------+--------------------------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------------------------------------------+---------------+-------------------+------+-----+---------+-------+---------------------------------+--------------------------------------+
| set_name | varchar(64) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | lookup; Size[MB]: 5.5; Ratio: 173.31 |
| set_type_name | varchar(64) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | lookup; Size[MB]: 0.1; Ratio: 999.99 |
| some_name | varchar(8) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | lookup; Size[MB]: 2.6; Ratio: 103.88 |
| some_num | int(11) | NULL | YES | | NULL | | select,insert,update,references | Size[MB]: 2.6; Ratio: 130.28 |
So could you comment if theses execution time for theses queries are normal ?
regards,
