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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
