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 [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -----Original Message----- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Thursday, August 19, 2004 10:48 AM To: matt ryan Cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
