Re: To index or not index .... Help needed

[HubbleSpace]

Why do you use such an big Primary key. As I know you should only use a Primary Key for your most used unique field. (preferly an ID)
The way you use the KEY makes only sense if you do an request like
SELECT ..
WHERE type=x AND year=y AND period=Z...col_nn_7=somthing
Is has no effect if you do a request only on field col_nn_6.

Query 1:
select account, sum(amount)
from facts
where type = 122 and year = 76
group by account

It would be faster if you have an INDEX (or KEY) on type
like:

CREATE TABLE `facts` (
`type` int(11) NOT NULL,
`year` int(11) NOT NULL,
....
INDEX(`type`),
INDEX(`year`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Depending on the situation only one INDEX could be better. This INDEX e.g. type should be the first in the query.

same with Query 2.

Use INDEX (or Key's ) only for fields that are very different and preferly not changed very often.

e.g. An INDEX for gender makes no sense.

An INDEX on your field amount seems to me worthless unless you request on this field or order it.

Hubble