Re: To index or not index .... Help needed
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
Subject
Views
Written By
Posted
2115
October 25, 2005 07:36AM
Re: To index or not index .... Help needed
1282
October 26, 2005 02:26AM
1316
October 26, 2005 04:46AM
1271
October 26, 2005 08:45AM
1371
October 26, 2005 02:14PM
1682
October 26, 2005 02:48PM
1352
October 27, 2005 03:47AM
1409
October 31, 2005 07:07AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.