Chris <[EMAIL PROTECTED]> writes: > chylli wrote: >> I run following command : >> use db1; >> insert into db2.c select a.a, a.b, a,c, b.d, b,e ... from a left join b on >> (a.id=b.id); > > Do you have an index on a.id and b.id ? > >> size of table a and table b is: >> [EMAIL PROTECTED] ls -l ../db1/a.* >> -rw-rw---- 1 mysql mysql 9230 May 10 15:41 ../db1/a.frm >> -rw-rw---- 1 mysql mysql 880880528 Jul 17 01:15 ../db1/a.MYD >> -rw-rw---- 1 mysql mysql 383653888 Jul 17 01:15 ../db1/a.MYI >> [EMAIL PROTECTED] ls -l ../db1/b.* >> -rw-rw---- 1 mysql mysql 11277 May 10 15:47 ../db1/b.frm >> -rw-rw---- 1 mysql mysql 1494998192 Jul 17 01:15 ../db1/b.MYD >> -rw-rw---- 1 mysql mysql 619606016 Jul 17 01:15 ../db1/b.MYI >> It has took 16776 seconds , and now the command is still >> running. the size of table c is >> [EMAIL PROTECTED] ls -l c.* >> -rw-rw---- 1 mysql mysql 11623 Jul 27 05:37 c.frm >> -rw-rw---- 1 mysql mysql 4633395200 Jul 27 10:08 c.MYD >> -rw-rw---- 1 mysql mysql 165481472 Jul 27 10:08 c.MYI >> the number of records in a is : >> 3101692 >> my question is : >> why is size of c big?
but size of c >> a+b now, now c is about 5G, and a+b is about 2.3G > > Because you're adding all columns from a and b into it. > >> It is too slow. Has anyone better methods to do that work? > > Drop the indexes on c and create them at the end. thanks for your help. I'll do like what you said in the following work. > > Each row that's being added, it's updating the index at the same time, > so that will be your bottleneck. > > > -- > 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]
