The test server has 48GB RAM, 8 Cores, RAID 10 with 6 SAS Servers HDs. I think the hardware should suffice. The config is adopted from a live server. I have changed the key_buffer_size to keep all myisam indexes inside the RAM.
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| bulk_insert_buffer_size | 67108864 |
| innodb_buffer_pool_size | 1073741824 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 8388608 |
| key_buffer_size | 34359738368 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 8388608 |
| sql_buffer_result | OFF |
+-------------------------+-------------+
mysql> SHOW TABLE STATUS LIKE 'strip_MetaData'\G
*************************** 1. row ***************************
Name: strip_MetaData
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 82690424
Avg_row_length: 113
Data_length: 9422759008
Max_data_length: 281474976710655
Index_length: 19449497600
Data_free: 0
Auto_increment: NULL
Create_time: 2010-08-27 10:18:45
Update_time: 2010-09-16 12:02:52
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
mysql> SHOW TABLE STATUS LIKE 'strip_CostCenter'\G
*************************** 1. row ***************************
Name: strip_CostCenter
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 197
Avg_row_length: 20
Data_length: 3940
Max_data_length: 281474976710655
Index_length: 5120
Data_free: 0
Auto_increment: 198
Create_time: 2010-08-27 10:18:44
Update_time: 2010-09-16 12:02:53
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
mysql> SHOW TABLE STATUS LIKE 'strip_ApprovalNumber'\G
*************************** 1. row ***************************
Name: strip_ApprovalNumber
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 16041
Avg_row_length: 23
Data_length: 384964
Max_data_length: 281474976710655
Index_length: 233472
Data_free: 0
Auto_increment: 16042
Create_time: 2010-08-27 10:18:44
Update_time: 2010-09-16 12:02:52
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
mysql> SHOW TABLE STATUS LIKE 'strip_Date'\G
*************************** 1. row ***************************
Name: strip_Date
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 2063
Avg_row_length: 12
Data_length: 24756
Max_data_length: 3377699720527871
Index_length: 31744
Data_free: 0
Auto_increment: 2064
Create_time: 2010-08-27 10:18:44
Update_time: 2010-09-16 11:21:44
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Slow query, because in 1. row is no key used but available:
mysql> explain
-> SELECT *
-> FROM
-> strip_Date d,
-> strip_CostCenter cc,
-> strip_ApprovalNumber an,
-> strip_MetaData md
-> WHERE
-> d.id = md.date_id
-> and cc.id = md.costCenter_id
-> and d.date = '2010-07-08'
-> and cc.costcenter = 21600
-> and an.approvalNumber = 211517279
-> and md.approvalNumber_id = an.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: md
type: ALL
possible_keys: fk__metadata__approvalNumber_id,fk__metadata__costCenter_id,fk__metadata__date_id
key: NULL
key_len: NULL
ref: NULL
rows: 82690424
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: archive.md.date_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: an
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: archive.md.approvalNumber_id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: cc
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: archive.md.costCenter_id
rows: 1
Extra: Using where
Fast query with same result but without joins but using key in 1. row:
explain SELECT
-> *
-> FROM
-> strip_Date d,
-> strip_MetaData md
-> WHERE
-> d.id = md.date_id
-> and md.costCenter_id = 107
-> and d.date = '2010-07-08'
-> and md.approvalNumber_id = 15101
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: md
type: ref
possible_keys: fk__metadata__approvalNumber_id,fk__metadata__costCenter_id,fk__metadata__date_id
key: fk__metadata__approvalNumber_id
key_len: 9
ref: const
rows: 865
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: archive.md.date_id
rows: 1
Extra: Using where
Show create tables:
mysql> show create table strip_Date\G
*************************** 1. row ***************************
Table: strip_Date
Create Table: CREATE TABLE `strip_Date` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2064 DEFAULT CHARSET=latin1
mysql> show create table strip_CostCenter\G
*************************** 1. row ***************************
Table: strip_CostCenter
Create Table: CREATE TABLE `strip_CostCenter` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`costCenter` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=198 DEFAULT CHARSET=latin1
mysql> show create table strip_ApprovalNumber\G
*************************** 1. row *************************
Table: strip_ApprovalNumber
Create Table: CREATE TABLE `strip_ApprovalNumber` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`approvalNumber` varchar(9) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16042 DEFAULT CHARSET=latin1
show create table strip_MetaData\G
*************************** 1. row ***************************
Table: strip_MetaData
Create Table: CREATE TABLE `strip_MetaData` (
`readingPoint_id` bigint(20) DEFAULT NULL,
`data_id` bigint(20) DEFAULT NULL,
`approvalNumber_id` bigint(20) DEFAULT NULL,
`deviceNumber_id` bigint(20) DEFAULT NULL,
`mandator_id` bigint(20) DEFAULT NULL,
`costCenter_id` bigint(20) DEFAULT NULL,
`wimConnector_id` bigint(20) DEFAULT NULL,
`manufacturer_id` bigint(20) DEFAULT NULL,
`series_id` bigint(20) DEFAULT NULL,
`date_id` bigint(20) DEFAULT NULL,
`stripSource` varchar(1) DEFAULT NULL,
`stripType` enum('L','NL') DEFAULT NULL,
`cashDate` datetime DEFAULT NULL,
`creationDate` datetime DEFAULT NULL,
KEY `fk__metadata__data_id` (`data_id`),
KEY `fk__metadata__approvalNumber_id` (`approvalNumber_id`),
KEY `fk__metadata__deviceNumber_id` (`deviceNumber_id`),
KEY `fk__metadata__mandator_id` (`mandator_id`),
KEY `fk__metadata__costCenter_id` (`costCenter_id`),
KEY `fk__metadata__wimConnector_id` (`wimConnector_id`),
KEY `fk__metadata__manufacturer_id` (`manufacturer_id`),
KEY `fk__metadata__series_id` (`series_id`),
KEY `fk__metadata__date_id` (`date_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1