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.
- 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
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
Also if MyIsam, is your row pointer large enough? If not this will slow you
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.
Boyd E. Hemphill
MySQL Certified Professional
O: (512) 248-2287
M: (713) 252-4688
From: Dan Nelson [mailto:dnelson@stripped]
Sent: Thursday, August 19, 2004 10:48 AM
To: matt ryan
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
> 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
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1