He's dropping the table just prior to running the problematic query, so I don't think optimizing the destination table will make a difference. Optimizing the source might speed it up a little, but he's looking at a difference of 2 minutes to 74 minutes if I'm understanding correctly when simply SELECTing vs INSERT SELECTing.
Mike, some thoughts - 1 - 7000 rows isn't that many. It should be very fast to insert that many rows in a simple table like yours. Is it possible you have something else going on, like a failing hard disk? Either where you store your data, or where your OS would use it for tmp or swap (or anything else, for that matter). 2 - if you create a plain SQL file to insert 7000 rows, without the SELECT DISTINCT, how long does it take to complete? 3 - is your server paging memory to disk? If you configure MySQL to use a lot more memory than you have physical RAM, your OS will start paging to disk, which can really slow down all operations. How much RAM have you got and how much are you using in MySQL for buffers cache etc.? 4 - have you triple-checked that the destination table is MyISAM with SHOW TABLE STATUS LIKE "tablename"? I would expect InnoDB to be somewhat slower, but honestly not this much slower ..... 5 - what happens if you simply swap in the same my.cnf file you used with 4.x ? Dan On 10/7/06, Angelo Zanetti <[EMAIL PROTECTED]> wrote:
mos wrote: > I have a simple query in MySQL 5.0.24: > > insert into table1 (col1) select distinct col1 from bigtable; > > that will run for 1:14:18. Both tables are MyISAM and table1 was just > created with 2 columns and is empty. > > The "select distinct col1 from bigtable" takes only 2 minutes to run > if I run it by itself (without the Insert statement), so why does > inserting it into Table1 take over an hour? This worked fine under > MySQL 4.1.10 > > BigTable has 30 million rows in it and will return approx 7000 > distinct values. > > TIA > > Mike > are your tables indexed? is col1 a primary key? have a look at optimising the table HTH -- 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]
