Re: 1 day 28 min insert

[Dan Nelson]

In the last episode (Aug 19), matt ryan said:
> >One alternative, since you know you don't have many records to insert,
> >is to pull the IDs of the missing records and insert just those.  Do an
> >outer join on the two tables (joining on the primary key), get a list
> >of the IDs of records in stat_in but not in 321st_stat, and add a
> >"WHERE id IN (list,of,ids)" clause to the end of your INSERT ... SELECT
> >statement.  If you're running 4.1, you can use a subquery and embed the
> >first query directly in the INSERT.
> 
> Running 4.0.x
> 
> something like, select a.*, b.* from a left outer join b on 
> a.col1=b.col1, a.col2=b.col2, a.col3=b.col3 where b.col1 is null into 
> temptable
> 
> then insert from temptable into table a
> 
> I think I tried this once, but it ran all day 

The select, or the insert?  If the select, run an EXPLAIN and see if
it's using the wrong index or no index.
 
> Is there a way to join on an index, instead of on each column?   The 
> primary key is 6-8 columns I forget

You have to list each column.  "SHOW KEYS FROM table" or "SHOW CREATE
TABLE table" will list all the keys and which fields are used in each
key.

-- 
	Dan Nelson
	dnelson@stripped