At 09:37 PM 10/6/2006, Dan Buettner wrote:
>Mike, was this a straight software upgrade on the same box? Any
>settings changes?
>
>Dan
Hi Dan,
When I installed MySQL 5.0 I used the My.ini for dedicated server
and edited it to use my old settings. I also noticed if I had InnoDb turned
on, access to MyISAM tables were 4x slower so I deactivated InnoDb and
MyISAM speeded up, except for this one query. Also if I just execute just
the "Select distinct col1 from bigtable" it executes right away (probably
still in the query cache). If I then execute:
insert into table1 (col1) select distinct col1 from bigtable;
it still takes > 1 hour to complete (it should only take a couple of
minutes at most). Table1 is dropped and re-created just prior to executing
the Insert statement so it's not corrupted. Col1 is the same column type in
both tables Char(17).
Any idea what's going on?
TIA
Mike
>On 10/6/06, mos <mos99@stripped> 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
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
