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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
