List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 27 2004 7:50pm
Subject:Re: 1 day 28 min insert
View as plain text  
I see two things I would change:

First, the column 321st_stat.dic is the first column of your primary key 
and has a second index on just it. That second index is redundant and 
could be deleted.

Second, in your WHERE clause you say : WHERE isnull(b.don) .  That forces 
the engine to run a function on every row of the b.don column to return a 
logical value. The simpler and faster thing to have said is :  WHERE b.don 
is null . That is a direct, native comparison and will use an index if one 
is available.

You _could_ try adding an identical primary key to the stat_in table as 
you have on the 321st_stat table. However, since we need all of the rows 
from stat_in in the results, I am not sure that it will help speed up the 
join (because it's a left join). Even though I think the index would be 
ignored, it is worth a try to see if it would make a difference with a new 
EXPLAIN..

Have you considered wrapping your insert with :
        ALTER TABLE `321st_stat` DISABLE KEYS
        ALTER TABLE `321st_stat` ENABLE KEYS
?


matt ryan <matt_lists@stripped> wrote on 08/27/2004 03:25:58 PM:

> 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/mysql?unsub=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