List:General Discussion« Previous MessageNext Message »
From:Boyd E. Hemphill Date:August 19 2004 7:11pm
Subject:RE: 1 day 28 min insert
View as plain text  
Matt:

You would be very well served to give the table a unique physical key.
Alternate or primary, you could then join to this single column.  I am not
sure how big the table to get, however, so you will definitely want to use a
bigint unsigned if you plan to test this theory.  

Benefits:
-  You can remove the 6 to 8 columns of the PK in the table you are writing
to and replace it with a small integer column.  Over 17m inserts and
comparisons you can imagine the savings in disk IO.
-  Easier for the optimizer to choose the correct index.
-  More likely to get reads straight from the index.
-  Easier code to write/read/maintain

Also, piling the records to be inserted into a temporary table can be much
quicker.  This is my experience with tables of 2 to 3 million rows.  B/c
this operation may still be long I do not recommend the use of a temporary
table, but rather a regular table that you create and destroy as part of the
process.

Are you using MyIsam or InnoDB?  If MyIsam, then if you are scheduling the
insert as delayed and the table is still being read from, you may be
experiencing an issue where there are enough reads to keep the insert from
getting started.

Also if MyIsam, is your row pointer large enough?  If not this will slow you
down too.  

Have the tables been analyzed and checked lately?

Really, there are a number of server parameters to check.  Setting this will
be very unique to your situation.  Oracle is the same way, esp when
clustering.   Seems to me that spending $1500 or so on some MySQL consulting
would be much less expensive than an Oracle license, plus the cost of
porting, plus the consulting it would take to get your Oracle server tuned
for your app.  

Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
bhemphill@stripped
Triand, Inc.
www.triand.com
O:  (512) 248-2287
M:  (713) 252-4688

-----Original Message-----
From: Dan Nelson [mailto:dnelson@stripped] 
Sent: Thursday, August 19, 2004 10:48 AM
To: matt ryan
Cc: mysql@stripped
Subject: Re: 1 day 28 min insert

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

-- 
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