From: Angelo Zanetti
Date: October 7 2006 11:29am
Subject: Re: MySQL 5 query takes 100x longer than MySQL 4.1.10
List-Archive: http://lists.mysql.com/mysql/202386
Message-Id: <45278FA2.6010405@zlogic.co.za>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



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