MySQL Forums
Forum List  »  Performance

Re: Mysql too Slow when Copying to Tmp Table
Posted by: karen wang
Date: November 11, 2012 07:53PM

thanks for your answer.

The following are some of the informations:
 
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)
 
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)
 
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

 
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
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)
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)



Edited 3 time(s). Last edit at 11/11/2012 07:58PM by karen wang.

Options: ReplyQuote


Subject
Views
Written By
Posted
12167
November 10, 2012 07:57AM
Re: Mysql too Slow when Copying to Tmp Table
5542
November 11, 2012 07:53PM


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.