Hery Yulianto a écrit : > > ----- Original Message ----- > From: "David Phillips" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, February 04, 2002 4:01 PM > Subject: Re: How to index Optimization on 20 million record > > > > PRIMARY KEY (`FIELD_1`), > > > KEY `FIELD_1` (`FIELD_1`), > > > > You don't need a second key on the column, as it is a duplicate of the > > primary key. > > of cource FIELD_2 also primary key, sorry i miss information > > CREATE TABLE `TABLE_1`( > `FIELD_1` varchar(64) NOT NULL default '', > `FIELD_2` varchar(16) NOT NULL default '', > `FIELD_3` varchar(150) default NULL '', > PRIMARY KEY (`FIELD_1`,'`FIELD_2`), > KEY `FIELD_1` (`FIELD_1`), > ) TYPE=MyISAM PACK_KEYS=1 > > but, why still long time to got the result from this query : select > count(*) from TABLE_1 where FIELD_1 = "XXX_CC"; > > any error from my structure database or whatever > > thank's > Hery Yulianto > Hi,
You don't need to set and index on FIELD_1, MySQL can use partial indexes (see http://www.mysql.com/doc/M/y/MySQL_indexes.html) and in your case can use the primary key. What does EXPLAIN tell you for this request ? Is your query buffer size optimized ? What values do you get for Key_blocks_used, Key_read_requests, and Key_reads with SHOW STATUS ? If your query buffer is too small to hold all your indexes, you may actually get better performance by droping redundant indexes. Regards -- Joseph Bueno NetClub/Trader.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php