mysql> SHOW STATUS LIKE ‘Table%’;
+———————–+———+
| Variable_name | Value |
+———————–+———+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+———————–+———+
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
Note: “ insert_table” is a memory table, and after above operation, TRUNCATE this table, not delete *.
The following content is a test of MySQL memory temporary table, I import 0.2m records into another table everyday, that table is becoming larger and larger, and SELECT is more and more slow, to solve these problems, temporary table is used in middle processing.
The detail explanation can be found in MySQL official website.
=========================================
DROP TABLE IF EXISTS `tmp_trade`;
CREATE TABLE IF NOT EXISTS `tmp_trade` (
`ID` bigint(20) NOT NULL,
`date` date NOT NULL,
`time` time DEFAULT NULL,
`code` char(6) DEFAULT NULL,
`volume` bigint(20) DEFAULT NULL,
`price` float DEFAULT NULL,
`money` bigint(20) DEFAULT NULL,
`property` char(2) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=Memory DEFAULT CHARSET=gb2312;
drop table tmp_trade;
