Skip navigation links

MySQL Forums :: Performance :: Mysql too Slow when Copying to Tmp Table


Advanced Search

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 5578 karen wang 11/10/2012 07:57AM
Re: Mysql too Slow when Copying to Tmp Table 3223 Rick James 11/11/2012 12:30PM
Re: Mysql too Slow when Copying to Tmp Table 2954 karen wang 11/11/2012 07:53PM
Re: Mysql too Slow when Copying to Tmp Table 2949 Rick James 11/11/2012 08:32PM
Re: Mysql too Slow when Copying to Tmp Table 2304 karen wang 11/12/2012 12:08AM


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.