List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 19 2004 4:48pm
Subject:Re: 1 day 28 min insert
View as plain text  
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
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