MySQL Forums
Forum List  »  Newbie

Question about merging big tables?
Posted by: H Lee
Date: March 16, 2009 12:49PM

Hi, folks:

In my applications, I need to merge three big tables(with different structures) into one big table, my SQL is
===========================================
DROP TABLE IF EXISTS `my_final_table`;
CREATE TABLE `my_final_table` (
...
UNIQUE KEY (`field1`,`field2`,`field3`,`source`),
);

INSERT INTO `my_final_table` SELECT ***** FROM `table1`;
REPLACE INTO `my_final_table` SELECT ***** FROM `table2`;
REPLACE INTO `my_final_table` SELECT ***** FROM `table3`;
===========================================

There is one unique key in 'mytable' with source corresponding to table[123] and the above SELECT clauses for three tables are very different..

This works very well when the three tables are not very big. Now I have total 4M records in three tables, and the merging SQL takes a very long time and at the end it yields the following error information:
-----------------------------------------------------------------
ERROR 1206 (HY000) at line 94: The total number of locks exceeds the lock table size
-----------------------------------------------------------------

MY question is: are there optimization parameters or better SQL that I can use to optimize this SQL?? Many thanks..

Hao

Options: ReplyQuote


Subject
Written By
Posted
Question about merging big tables?
March 16, 2009 12:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.