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 th
- Original Message -
From: [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 ext
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 p
> 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
ROTECTED]>
Cc: <[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 ba
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 functi
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
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, 200
Mikhail Entaltsev wrote:
Hi,
insert into 321st_stat select * from stat_in group by ;
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 dropped me back to the
command prompt, wit
Hi,
insert into 321st_stat select * from stat_in group by ;
did you try to use this query?
Best regards,
Mikhail.
- Original Message -
From: "matt ryan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 19, 2004 6:06 PM
Subject: 1 day 28 min insert
> I think oracle
you never know.
Donny
> -Original Message-
> From: Remigiusz Sokołowski [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,
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 I
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 Mail
, 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:
> &
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
Unimin
It could save you lots of processing time to pre-collect a list of the
records you are actually going to add to the table. I know it sounds
counter-intuitive but this has saved me OODLES of time on several
occasions (of course it can't work for all situations but in your case
where you are NOT
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 sta
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 inser
- 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, b
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 (list,
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_
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 inserts there are much more
22 matches
Mail list logo
