List:General Discussion« Previous MessageNext Message »
From:matt ryan Date:August 27 2004 7:25pm
Subject:Re: 1 day 28 min insert
View as plain text  
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 |

Thread
1 day 28 min insertmatt ryan19 Aug
  • Re: 1 day 28 min insertAndrew Pattison19 Aug
    • Re: 1 day 28 min insertmatt ryan19 Aug
      • Re: 1 day 28 min insertSGreen19 Aug
    • Re: 1 day 28 min insertSGreen19 Aug
  • Re: 1 day 28 min insertDan Nelson19 Aug
    • Re: 1 day 28 min insertmatt ryan19 Aug
      • Re: 1 day 28 min insertDan Nelson19 Aug
        • RE: 1 day 28 min insertBoyd E. Hemphill19 Aug
          • Re: 1 day 28 min insertmatt ryan19 Aug
            • Re: 1 day 28 min insertRemigiusz SokoĊ‚owski20 Aug
              • RE: 1 day 28 min insertDonny Simonton20 Aug
  • Re: 1 day 28 min insertRhino19 Aug
  • Re: 1 day 28 min insertMikhail Entaltsev20 Aug
    • Re: 1 day 28 min insertmatt ryan27 Aug
  • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
    • Re: 1 day 28 min insertmatt ryan27 Aug
      • Re: 1 day 28 min insertSGreen27 Aug
        • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
          • Re: 1 day 28 min insertSGreen27 Aug
            • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
          • Re: 1 day 28 min insertmatt ryan1 Sep
  • Re: 1 day 28 min insertMikhail Entaltsev27 Aug