0

I am using MySQL v5.1.

My simple scenario is that I would like to import a large amount of data from a table in one database to another table in another database. And the table in another database (to where the data is going to be inserted) will be truncated first.

To be detailed, Say I have two databases, named 'db_one' and 'db_two' respectively.

db_one has a table named db_one_cars which contains 6,000,000 records (which is a big amount of data). While db_two has a table named db_two_cars which contains also a big amount of data.

I am going to firstly remove all the data in db_two_cars (truncate the table), then import all the data from db_one_cars to db_two_cars. (Note: the two tables are in two databases respectively).

So, I created a sql file with content like following:

TRUNCATE TABLE db_two_cars;

ALTER TABLE db_two_cars DISABLE KEYS;

INSERT INTO db_two_cars  (car_id, name, customer, company_name)
SELECT id, CONCAT('c-', name), customer, company_name FROM db_one.db_one_cars;

ALTER TABLE db_two_cars ENABLE KEYS;

both db_one_cars and db_two_cars tables are InnoDB table and have the same structure, car_id is used as primary key, company_name is used as multi-column key, customer is used as multi-column key.

I would like to speed up this large amount of data importing, and also want a good performance to query the table after improting data.

my questions:

  1. Is it needed to have OPTIMIZE TABLE db_two_cars at the end of my SQL statement? and why? (will it improve the performance? I am merely inserting data)
  2. Do I need to use ALTER TABLE db_two_cars PACK_KEYS = 0 ? and why? (will it improve the performance)
  3. What other things can I do to improve the speed and future query performance?

P.S. The performance I mean includes speed up the data importing and good performance for future data querying

Mellon
  • 33,620
  • 73
  • 177
  • 259

1 Answers1

1

to improve this do export of the data to csv file

and load this file to the new table using :

load data infile

another tips to speed this u can look at this answer

Community
  • 1
  • 1
Haim Evgi
  • 114,996
  • 43
  • 205
  • 218
  • You mean load data infile is much faster than the way I used? How to deal with the indexes then? – Mellon Nov 24 '11 at 09:41
  • about the dealing with index see this : http://stackoverflow.com/questions/827445/what-mysql-settings-affect-the-speed-of-load-data-infile – Haim Evgi Nov 24 '11 at 09:42
  • I am still interested about my 1st and 2nd questions though load data infile could be a good option. If anyone can provide also an answer of my first 2 questions. – Mellon Nov 24 '11 at 09:46