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]

Reply via email to