Mikhail Entaltsev wrote:
You _could_ try adding an identical primary key to the stat_in table as
you have on the 321st_stat table. However, since we need all of the rows
from stat_in in the results, I am not sure that it will help speed up the
join (because it's a left join). Even though I think
Mikhail Entaltsev wrote:
Hi,
insert into 321st_stat select * from stat_in group by primary key fields
from 321st_stat table;
did you try to use this query?
Best regards,
Mikhail.
Ran it, it took at least 24 hours, it finished but never gave me the
total time, when I checked the server mysql
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
Best regards, Mikhail.
- Original Message -
From: matt ryan [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, August 27, 2004 4:45 PM
Subject: Re: 1 day 28 min insert
Mikhail
Mikhail Entaltsev wrote:
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
I have no key's on the temp table, stat_in, do you think adding keys on
the whole primary key would be faster?
I wasnt sure if you could join mysql keys, the key is
I see two things I would change:
First, the column 321st_stat.dic is the first column of your primary key
and has a second index on just it. That second index is redundant and
could be deleted.
Second, in your WHERE clause you say : WHERE isnull(b.don) . That forces
the engine to run a
: [EMAIL PROTECTED]
Sent: Friday, August 27, 2004 9:25 PM
Subject: Re: 1 day 28 min insert
Mikhail Entaltsev wrote:
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
I have no key's on the temp table, stat_in, do you think adding keys
You _could_ try adding an identical primary key to the stat_in table as
you have on the 321st_stat table. However, since we need all of the rows
from stat_in in the results, I am not sure that it will help speed up the
join (because it's a left join). Even though I think the index would be
If that were a valid SQL statement, I would agree with you. What you wrote
**only** functions through a MySQL-specific SQL extension that permits
non-aggregated, non-grouped columns to exist in the SELECT clause.
I do not understand why you believe that a GROUP BY test will prove any
sort of
: [EMAIL PROTECTED]
To: Mikhail Entaltsev
Cc: matt ryan ; [EMAIL PROTECTED]
Sent: Friday, August 27, 2004 10:40 PM
Subject: Re: 1 day 28 min insert
If that were a valid SQL statement, I would agree with you. What you wrote
**only** functions through a MySQL-specific SQL extension that permits
non
matt ryan wrote:
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every column, if I were
to make it one using concat, it would be huge.
I tried making those fields a hash, but it did not work, I had
duplicate hashes for non duplicate records!!
Matt
If
Message-
From: Remigiusz Sokoowski [mailto:[EMAIL PROTECTED]
Sent: Friday, August 20, 2004 1:12 AM
To: matt ryan; [EMAIL PROTECTED]
Subject: Re: 1 day 28 min insert
matt ryan wrote:
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every
day 28 min insert
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files, but daily sql's
dont work very well, and miss records, in this case it missed 563 records.
mysql update stat_in set ctasc='321ST';
Query OK, 77269086
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files, but daily sql's
dont work very well, and miss records, in this case it missed 563 records.
mysql update stat_in set ctasc='321ST';
Query OK, 77269086 rows affected (24 min
disk IOs required.
Cheers
Andrew.
- Original Message -
From: matt ryan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 19, 2004 5:06 PM
Subject: 1 day 28 min insert
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get
In the last episode (Aug 19), matt ryan said:
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files, but daily sql's
dont work very well, and miss records, in this case it missed 563 records.
mysql update stat_in set
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
- Original Message -
From: matt ryan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 19, 2004 12:06 PM
Subject: 1 day 28 min insert
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files, but daily
Andrew Pattison wrote:
I'm guessing that you have indexes on the 321st_stat table? If this is
the case, try dropping them before you do the insert, then rebuilding
them. MySQL is known to be slow at doing bulk inserts on indexed
tables. Also, updates are much faster than inserts since with
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
.
- Original Message -
From: matt ryan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 19, 2004 5:06 PM
Subject: 1 day 28 min insert
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files
You are DEFINITELY not going to win any races with INSERT REPLACE.
Minimizing the number of records you need to process will improve your
times significantly, especially since you need less than .002% of your
source data added to your destination table.
Shawn Green
Database Administrator
, 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
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every column, if I were
to make it one using concat, it would be huge.
I tried making those fields a hash, but it did not work, I had duplicate
hashes for non duplicate records!!
Matt
--
MySQL General
23 matches
Mail list logo
