Re: How to see key_buffer in action
Posted by:
karen wang
Date: November 11, 2012 06:58PM
thank you for your answer
some information:
[ code ]
mysql> show create table base_info\G
*************************** 1. row ***************************
Table: base_info
Create Table: CREATE TABLE `base_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`phase` int(11) NOT NULL DEFAULT '1',
`code_region_oid` decimal(10,0) DEFAULT NULL,
`code_urban_rural_oid` decimal(10,0) DEFAULT NULL,
`exam_id` int(11) NOT NULL,
`exam_name` varchar(45) DEFAULT NULL,
`location` varchar(200) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth_date` date DEFAULT NULL,
`code_gender_oid` int(11) DEFAULT NULL,
`code_race_oid` int(11) DEFAULT NULL,
`code_education_level_oid` int(11) DEFAULT NULL,
`code_occupation_oid` int(11) DEFAULT NULL,
`code_marriage_oid` int(11) DEFAULT NULL,
`native_place` varchar(45) DEFAULT NULL,
`birthplace` varchar(200) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `base_info_indx1` (`code_gender_oid`,`code_race_oid`,`code_region_oid`),
KEY `base_info_indx2` (`id`,`code_gender_oid`,`code_race_oid`,`code_region_oid`)
) ENGINE=MyISAM AUTO_INCREMENT=40916 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[ /code ]
[ code ]
mysql> show create table physical_info\G
*************************** 1. row ***************************
Table: physical_info
Create Table: CREATE TABLE `physical_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`base_info_id` int(11) NOT NULL,
`height` decimal(11,2) DEFAULT NULL,
`weight` decimal(11,2) DEFAULT NULL,
`sitting_height` decimal(11,2) DEFAULT NULL,
`hip_circumference` decimal(11,2) DEFAULT NULL,
`chest_circumference` decimal(11,2) DEFAULT NULL,
`waist_circumference` decimal(11,2) DEFAULT NULL,
`systolic_blood_pressure` decimal(11,0) DEFAULT NULL,
`diastolic_blood_pressure` decimal(11,0) DEFAULT NULL,
`heart_rate` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_physical_info_base_info1` (`base_info_id`)
) ENGINE=MyISAM AUTO_INCREMENT=40916 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[ /code ]
[ code ]
mysql> show table status like 'base_info' \G;
*************************** 1. row ***************************
Name: base_info
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 40915
Avg_row_length: 59
Data_length: 2437416
Max_data_length: 281474976710655
Index_length: 2473984
Data_free: 0
Auto_increment: 40916
Create_time: 2012-11-12 08:37:08
Update_time: 2012-11-12 23:42:30
Check_time: 2012-11-12 08:37:09
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
[ /code ]
[ code ]
mysql> show table status like 'physical_info' \G;
*************************** 1. row ***************************
Name: physical_info
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 40915
Avg_row_length: 54
Data_length: 2209410
Max_data_length: 15199648742375423
Index_length: 840704
Data_free: 0
Auto_increment: 40916
Create_time: 2012-11-10 13:59:27
Update_time: 2012-11-11 05:04:35
Check_time: 2012-11-10 14:02:50
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
[ /code ]
[ code ]
mysql> explain select b.code_gender_oid, b.code_race_oid, b.code_region_oid, avg(a.height), stddev(a.height), avg(a.height) - 1.96 * STDDEV(a.height) refa, avg(a.height) + 1.96 * STDDEV(a.height) refb from base_info b left join physical_info a on b.id = a.base_info_id group by b.code_gender_oid, b.code_race_oid, b.code_region_oid;
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
| 1 | SIMPLE | b | index | NULL | base_info_indx2 | 20 | NULL | 40915 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | fk_physical_info_base_info1 | fk_physical_info_base_info1 | 4 | CAMS_physiology.b.id | 1 | |
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
[ /code ]
[ code ]
mysql> show variables like '%buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 134217728 |
| innodb_buffer_pool_size | 536870912 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 209715200 |
| key_buffer_size | 5368709120 |
| myisam_sort_buffer_size | 268435456 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 16777216 |
| read_rnd_buffer_size | 33554432 |
| sort_buffer_size | 16777216 |
| sql_buffer_result | OFF |
+-------------------------+------------+
12 rows in set (0.00 sec)
[ /code ]