First of all, IMHO index 321st_stat.dic is useless since you have dic as the first field in 321st_stat.PRIMARY KEY. Second, I would recommend add KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table and set PACK_KEYS=0 for stat_in table.
Then measure execution time of select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; If it is relatively short then the problem is in number of keys and PACK_KEYS for 321st_stat table. Please, let me know about your results. Best regards, Mikhail. ----- Original Message ----- From: "matt ryan" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 27, 2004 9:25 PM Subject: Re: 1 day 28 min insert > Mikhail Entaltsev wrote: > > >Could you execute "show create table 321st_stat" and "show create table > >stat_in" > >and send results back? > > > > > > I have no key's on the temp table, stat_in, do you think adding keys on > the whole primary key would be faster? > > I wasnt sure if you could join mysql keys, the key is called "primary > key" so would it be a."primary key" = b."primary key" ? > > > > > mysql> explain select a.* from stat_in a left outer join 321st_stat b on > a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and > a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn > =b.dte_txn where isnull(b.don); > > | id | select_type | table | type | possible_keys | > key | key_len | ref | rows | > Extra | > | 1 | SIMPLE | a | ALL | NULL | > NULL | NULL | NULL | 77269086 > | | > | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | > PRIMARY | 39 | > finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a.suf,finlog. a.dte_txn,finlog.a.sta > | 1 | Using where; Not exists | > 2 rows in set (0.11 sec) > > -------------------+ > | 321st_stat | CREATE TABLE `321st_stat` ( > `dic` char(3) NOT NULL default '', > `fr_ric` char(3) NOT NULL default '', > `niin` char(11) NOT NULL default '', > `ui` char(2) NOT NULL default '', > `qty` char(5) NOT NULL default '', > `don` char(14) NOT NULL default '', > `suf` char(1) NOT NULL default '', > `dte_txn` char(5) NOT NULL default '', > `ship_to` char(3) NOT NULL default '', > `sta` char(2) NOT NULL default '', > `lst_sos` char(3) NOT NULL default '', > `esd` char(4) NOT NULL default '', > `stor` char(3) NOT NULL default '', > `d_t` char(4) NOT NULL default '', > `ctasc` char(10) NOT NULL default '', > PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), > KEY `don` (`don`), > KEY `niin` (`niin`), > KEY `stor` (`stor`), > KEY `dic` (`dic`), > KEY `ctasc` (`ctasc`), > KEY `dte_txn` (`dte_txn`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | > 1 row in set (0.03 sec) > > | stat_in | CREATE TABLE `stat_in` ( > `dic` char(3) NOT NULL default '', > `fr_ric` char(3) NOT NULL default '', > `niin` char(11) NOT NULL default '', > `ui` char(2) NOT NULL default '', > `qty` char(5) NOT NULL default '', > `don` char(14) NOT NULL default '', > `suf` char(1) NOT NULL default '', > `dte_txn` char(5) NOT NULL default '', > `ship_to` char(3) NOT NULL default '', > `sta` char(2) NOT NULL default '', > `lst_sos` char(3) NOT NULL default '', > `esd` char(4) NOT NULL default '', > `stor` char(3) NOT NULL default '', > `d_t` char(4) NOT NULL default '', > `ctasc` char(10) NOT NULL default '' > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
