CREATE TABLE test_table_type_one (
measure1_id BIGINT NOT NULL,
year_start_id BIGINT NOT NULL,
year_end_id BIGINT NOT NULL,
measure2_id BIGINT NOT NULL,
measure3_id BIGINT NOT NULL,
measure4_id BIGINT NOT NULL,
measure5_id BIGINT NOT NULL,
data_type_id BIGINT NOT NULL,
value DOUBLE PRECISION NOT NULL,
high_value DOUBLE PRECISION NULL,
low_value DOUBLE PRECISION NULL,
INDEX comp_idx (measure2_id ASC, measure5_id ASC, measure4_id ASC, year_start_id ASC, year_end_id ASC, data_type_id ASC, measure1_id ASC, measure3_id ASC),
FAMILY “primary” (measure1_id, year_start_id, year_end_id, measure2_id, measure3_id, measure4_id, measure5_id, data_type_id, val, upper, lower, rowid)
Count(*) on this table is comparable with MySQL.
The other type of table on which the group by is slow, has year_start_id and year_end_id replaced by year_id. Other columns are the same.
The fat index covers all columns our queries will join them by.