MySQL Forums :: Newbie :: Question about merging big tables?


Advanced Search

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? H Lee 03/16/2009 12:49PM
Re: Question about merging big tables? H Lee 03/17/2009 09:59AM
Re: Question about merging big tables? Rick James 03/18/2009 12:03AM


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.