Thanks RJ,
1. MYISAM tables
2.
UPDATE t1, t2
set t1.c1=t2.c1, t1.c2=t2.c2, t1.c3=t2.c3
where t1.c4=t2.c4
t1.c4 and t2.c4 are indexed. Was thinking about indexing maybe t1.c1, but would that help since they have to be evaluated one at a time anyway?
3. t1.c4 and t2.c4 turn out not to be unique. They are literals: the name of a particular produced "widget" as a numeric reference. ie 6439932 is a blue widget with furry trim, 6439933 is a red widget with stripped trim, etc. Ran queries to group by c4 with count(*) and found that there can be over 1000 data instances of each in both tables for the same literal value. Therefore the requirements for the reports as given are nonfunctional. Have request for clarification with an explanation in. No doubt this could be/is at least a material part of the problem.
4. 8 gig ram. Using ubuntu linux, Monitor queries using system monitor. Did not exceed 1 gig ram with this query.
5 table info: The column names have been changed, privacy and security issues.
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`I` int(11) NOT NULL AUTO_INCREMENT,
`SH` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`OR` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`BO` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`AS` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`SH` date NOT NULL,
`RE` date NOT NULL,
`ES` date NOT NULL,
`SH` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`ST` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`IN` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`IN` date NOT NULL,
`TO` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`TO` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`FR` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`FR` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`CO` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`NO` varchar(4) COLLATE utf8_bin DEFAULT NULL,
`EX` varchar(17) COLLATE utf8_bin DEFAULT NULL,
`EX` varchar(17) COLLATE utf8_bin DEFAULT NULL,
`CO` varchar(160) COLLATE utf8_bin DEFAULT NULL,
`SE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`IT` varchar(25) COLLATE utf8_bin DEFAULT NULL,
`DI` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`RE` varchar(25) COLLATE utf8_bin DEFAULT NULL,
`CA` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`IN` varchar(2) COLLATE utf8_bin DEFAULT NULL,
`QT` decimal(12,4) DEFAULT NULL,
`UN` decimal(20,4) DEFAULT NULL,
`UN` decimal(20,4) DEFAULT NULL,
`QT` decimal(12,4) DEFAULT NULL,
`MA` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`UP` varchar(25) COLLATE utf8_bin DEFAULT NULL,
`IT` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`VE` varchar(60) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IT` (`IT`)
) ENGINE=MyISAM AUTO_INCREMENT=105307632 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 105307631
Avg_row_length: 169
Data_length: 17884691968
Max_data_length: 281474976710655
Index_length: 967736320
Data_free: 0
Auto_increment: NULL
Create_time: 2011-11-25 17:56:50
Update_time: 2011-11-25 18:03:25
Check_time: 2011-11-25 18:06:10
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+----------+
12 rows in set (0.00 sec)
SHOW CREATE TABLE t2\G;
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SH` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`PO` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`BO` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`AS` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`SH` date DEFAULT NULL,
`RE` date DEFAULT NULL,
`SH` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`CL` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`RC` int(14) DEFAULT NULL,
`PO` decimal(18,2) DEFAULT NULL,
`UN` decimal(18,2) DEFAULT NULL,
`PO` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`DE` int(8) DEFAULT NULL,
`BU` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`VN` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`ST` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`PR` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`PO` date DEFAULT NULL,
`SN` date DEFAULT NULL,
`SN` date DEFAULT NULL,
`PO` varchar(18) COLLATE utf8_bin DEFAULT NULL,
`BH` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`BH` decimal(18,2) DEFAULT NULL,
`SH` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`PO` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`EX` decimal(23,10) DEFAULT NULL,
`CU` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`PI` date DEFAULT NULL,
`PO` varchar(75) COLLATE utf8_bin DEFAULT NULL,
`VN` varchar(60) COLLATE utf8_bin DEFAULT NULL,
`FR` varchar(50) COLLATE utf8_bin NOT NULL,
`CS` int(12) DEFAULT NULL,
`IT` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`OR` decimal(18,2) DEFAULT NULL,
`QT` int(12) DEFAULT NULL,
`OR` int(12) DEFAULT NULL,
`QT` int(12) DEFAULT NULL,
`UN` decimal(18,2) DEFAULT NULL,
`VA` decimal(18,2) DEFAULT NULL,
`UN` decimal(18,2) DEFAULT NULL,
`RE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`IN` varchar(22) COLLATE utf8_bin DEFAULT NULL,
`UP` varchar(25) COLLATE utf8_bin DEFAULT NULL,
`LO` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`Bu` varchar(4) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `RC` (`RC`),
KEY `UN` (`UN`),
KEY `VN` (`VN`),
KEY `VN` (`VN`),
KEY `DE` (`DE`),
KEY `CL` (`CL`)
) ENGINE=MyISAM AUTO_INCREMENT=43660686 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 't2'\G;
*************************** 1. row ***************************
Name: t2
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 43660685
Avg_row_length: 287
Data_length: 12547954872
Max_data_length: 281474976710655
Index_length: 2646091776
Data_free: 0
Auto_increment: 43660686
Create_time: 2011-11-27 10:19:24
Update_time: 2011-11-27 10:43:13
Check_time: 2011-11-27 10:39:18
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Have no control over table structure/schema with source data tables which are as above -minus the auto increment primary field. Can do as needed other wise: create other tables etc.
Did I leave anything out?
Ed
Edited 1 time(s). Last edit at 11/28/2011 08:03AM by ed ziffel.