To index or not index .... Help needed
I have a small fact table with currently about 600000 rows which in the future will be a lot more. When using GROUP BY and SUM queries on the table I experience some strange performance behaviours I can't explain. If I remove all indexes queries runs considerably faster.
MySQL version: 5.0.13rc
Table def:
CREATE TABLE `facts` (
`type` int(11) NOT NULL,
`year` int(11) NOT NULL,
`period` int(11) NOT NULL,
`account` int(11) NOT NULL,
`col_nn_1` int(11) NOT NULL,
`col_nn_2` int(11) NOT NULL,
`col_nn_3` int(11) NOT NULL,
`col_nn_4` int(11) NOT NULL,
`col_nn_5` int(11) NOT NULL,
`col_nn_6` int(11) NOT NULL,
`col_nn_7` int(11) NOT NULL,
`amount` double NOT NULL,
PRIMARY KEY (`type`,`year`,`period`,`account`,`col_nn_1`,`col_nn_2`,`col_nn_3`,`col_nn_4`,`col_nn_5`,`col_nn_6`,`col_nn_7`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Record distribution for type and year:
type year count(*)
=====================
120 75 5708
120 76 5947
120 77 6247
122 74 114673
122 75 159251
122 76 187524
122 77 110290
Query 1:
==================================
select account, sum(amount)
from facts
where type = 122 and year = 76
group by account
Time: 529 rows fetched in 1,4921s
Explained:
id 1
select_type SIMPLE
table facts
type ref
possible_keys PRIMARY
key PRIMARY
key_len 8
ref const,const
rows 193572
Extra Using where; Using temporary; Using filesort
=======
Query 2:
==================================
select account, sum(amount)
from facts
where type = 122 and year in (76, 77)
group by account
Time: 561 rows fetched in 4,2622s
Explained:
id 1
select_type SIMPLE
table facts
type ref
possible_keys PRIMARY
key PRIMARY
key_len 4
ref const
rows 294820
Extra Using where; Using temporary; Using filesort
=======
If I now remove the primary key from the table, i.e no indexes at all I get the following results.
Query 1: 529 rows fetched in 0,2455s
Query 2: 561 rows fetched in 0,4790s
Both queries explained identically:
id 1
select_type SIMPLE
table facts
type ALL
possible_keys
key
key_len
ref
rows 589640
Extra Using where; Using temporary; Using filesort
Does anyone know what causes the dramatic slow down when using an index? Query 2 runs almost ten (10) times slower with an index than without.
Even if I define an index on type and year only I get the same performance decrease. Is there a table or server setting I've missed? I tried using InnoDB-engine with no result.