Re: 1 day 28 min insert

[matt ryan]

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]