n****u
发帖数: 229 | 1 Sorry I can not write Chinese here.
In MySQL, I have two databases: newdb and olddb
table1 in newdb, table2 and table3 in olddb
table1 has userid, nickname, msn
table2 has userid, nickname
table3 has userid msn
First I use NaviCat to import table2 into table1, very fast! 320,000 records
in 2 mins!
Then I tried to import table 3 into table1, now I have to check userid first
, then UPDATE table1. Very slow. 50,000 records in 12 hours
Anyway I can make it fast?
Thanks | n****u
发帖数: 229 | 2 If I innerjoin table2 and table3 first, will it be faster? | t*********i
发帖数: 217 | 3 yes you should join first then import.
My estimate is the total time would be comparable to only import table2. | n****u
发帖数: 229 | 4 How long will it take to inner join table2 and table 3 (each has 320,00
records)
【在 t*********i 的大作中提到】
: yes you should join first then import.
: My estimate is the total time would be comparable to only import table2.
| t*********i
发帖数: 217 | 5 That really depend on your system, how much memory, how much undo space ( I
am from Oracle side, but mysql should have have similar structure), do you
have indexes on tables or not. You need to talk to your DBA to know that.
If I need to do this, I probably will do the join and insert in chunks(id in
certain range is good candidate; again, you need to have good index in
place). commit in chunks will reduce requirement to system resources.
......................................................... | n****u
发帖数: 229 | 6 Thanks for your reply.
I test INNER JOIN:
INNER JOIN table 2 and table 3, 40 seconds (not sure it's good or bad for
320,000 records)
So I started to work on my real task
INNER JOIN table 2, table 3 and table 4, 3 mins (320,000 records)
export result to dbf file, 10 mins
import dbf into Table1, 10 mins
Finally I finished my task in half hour, instead of 3 days.
Now how can I improve export and import process?
PS. I am a .net programmer, but team leader asked me to migrate database.
The next task | B*****g
发帖数: 34098 | 7 1. check if table2, table3 has primary key userid.
if not, add.
2.
insert into tables1(userid, nickname, msn)
select a.userid, a.nickname, b.msn
from table2 a, table3 b
where a.userid = b.userid.
1. check if table1 has primary key userid.
if not, add.
Note: IF userid is not unique in table 2, table3, kick the person who design
these tables.
records
first
【在 n****u 的大作中提到】
: Sorry I can not write Chinese here.
: In MySQL, I have two databases: newdb and olddb
: table1 in newdb, table2 and table3 in olddb
: table1 has userid, nickname, msn
: table2 has userid, nickname
: table3 has userid msn
: First I use NaviCat to import table2 into table1, very fast! 320,000 records
: in 2 mins!
: Then I tried to import table 3 into table1, now I have to check userid first
: , then UPDATE table1. Very slow. 50,000 records in 12 hours
| B*****g
发帖数: 34098 | 8 tell them to start to use sql server, hehe
【在 n****u 的大作中提到】
: Thanks for your reply.
: I test INNER JOIN:
: INNER JOIN table 2 and table 3, 40 seconds (not sure it's good or bad for
: 320,000 records)
: So I started to work on my real task
: INNER JOIN table 2, table 3 and table 4, 3 mins (320,000 records)
: export result to dbf file, 10 mins
: import dbf into Table1, 10 mins
: Finally I finished my task in half hour, instead of 3 days.
: Now how can I improve export and import process?
| n****u
发帖数: 229 | 9 I love you both tangyuanlai and Beijing! hehe
Yes, I tried this way, it's sooooooo fast!
Insert new table
select
from inner join of old tables
21.891 ms (migrated 320,000 records)
Previously I used C# code, it would take me 1 week
Then I used NaviCat import wizard, it would take me 3 days
Yesterday I exported query result and imported into new table, it took me 30
mins
Now, 21.891 ms haha
Thank you very much!!! | B*****g
发帖数: 34098 | 10 check record number in 3 tables see if they match.
30
【在 n****u 的大作中提到】
: I love you both tangyuanlai and Beijing! hehe
: Yes, I tried this way, it's sooooooo fast!
: Insert new table
: select
: from inner join of old tables
: 21.891 ms (migrated 320,000 records)
: Previously I used C# code, it would take me 1 week
: Then I used NaviCat import wizard, it would take me 3 days
: Yesterday I exported query result and imported into new table, it took me 30
: mins
| n****u
发帖数: 229 | 11 Thank you reminding me.
If table 2 has an id=5, but table 3 does not have this id, what can I do?
【在 B*****g 的大作中提到】
: check record number in 3 tables see if they match.
:
: 30
| n****u
发帖数: 229 | 12 I check the number
Table2: 319,928
Table3: 319,927
Table4: 319,927
So my result has 319,927 records
How to find this missing one?
【在 B*****g 的大作中提到】
: check record number in 3 tables see if they match.
:
: 30
| B*****g
发帖数: 34098 | 13 SELECT CASE
WHEN a.userid IS NULL
THEN b.userid
ELSE a.userid
END AS userid,
a.nickname,
b.msn
FROM table2 a FULL OUTER JOIN table3 b ON a.userid = b.userid
【在 n****u 的大作中提到】
: Thank you reminding me.
: If table 2 has an id=5, but table 3 does not have this id, what can I do?
|
|
