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?
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.
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]