MySQL Forums
Forum List  »  Performance

Mysql too Slow when Copying to Tmp Table
Posted by: karen wang
Date: November 10, 2012 07:57AM

my server is
linux operator system with 65G ram 4 CPU 2core
when i excute this sql on my mysql server it's too slow
select b.code_gender_oid,
b.code_race_oid,
b.code_region_oid,
avg(a.height),
stddev(a.height)
from base_info b,
physical_info a FORCE INDEX(fk_heart_function_base_info1)
where a.base_info_id = b.id
group by b.code_gender_oid, b.code_race_oid, b.code_region_oid

some information:

mysql> show variables like '%table%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| big_tables | OFF |
| innodb_file_per_table | OFF |
| innodb_table_locks | ON |
| lower_case_table_names | 0 |
| max_heap_table_size | 209715200 |
| max_tmp_tables | 32 |
| old_alter_table | OFF |
| sql_big_tables | OFF |
| table_definition_cache | 10240 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 5000 |
| table_type | MyISAM |
| tmp_table_size | 209715200 |
| updatable_views_with_limit | YES |
+----------------------------+-----------+

mysql> show PROFILE for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000017 |
| checking query cache for query | 0.000102 |
| Opening tables | 0.020198 |
| System lock | 0.000010 |
| Table lock | 0.000023 |
| init | 0.000044 |
| optimizing | 0.000006 |
| statistics | 0.000029 |
| preparing | 0.000015 |
| Creating tmp table | 0.000044 |
| executing | 0.000003 |
| Copying to tmp table | 4.170332 |
| Sorting result | 0.000020 |
| Sending data | 0.000104 |
| end | 0.000002 |
| removing tmp table | 0.000006 |
| end | 0.000004 |
| query end | 0.000004 |
| freeing items | 0.000129 |
| storing result in query cache | 0.000006 |
| logging slow query | 0.000001 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
23 rows in set (0.00 sec)

what can i do for this "Copying to tmp table" problem.

Options: ReplyQuote


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


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.