Slow Query too much time in Copy to Tmp Table
Hello,
I have this query which is taking too long to execute most of the times. When I check the "show profile" for the query I can see it spends most of the time (several seconds) in "Copy to tmp table".
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000060 |
| checking query cache for query | 0.000120 |
| Opening tables | 0.000031 |
| System lock | 0.000018 |
| Table lock | 0.000070 |
| init | 0.000057 |
| optimizing | 0.000030 |
| statistics | 0.000135 |
| preparing | 0.000037 |
| Creating tmp table | 0.000099 |
| executing | 0.000015 |
| Copying to tmp table | 2.344832 |
| Sorting result | 0.333453 |
| Sending data | 0.359093 |
| end | 0.000037 |
| removing tmp table | 0.000976 |
| end | 0.000013 |
| query end | 0.000007 |
| freeing items | 0.000021 |
| removing tmp table | 0.000018 |
| freeing items | 0.000029 |
| storing result in query cache | 0.000355 |
| logging slow query | 0.000008 |
| logging slow query | 0.000049 |
| cleaning up | 0.000009 |
+--------------------------------+----------+
It's running in a server with RHEL, 16GB RAM and 2 QuadCom (8 processors total).
This is the query:
SELECT `visit_client`, COUNT(DISTINCT `sid`) as 'impr'
FROM `track_visits_store` FORCE INDEX (`visit_unique`), `track_acts_store`
WHERE `visit_unique`=`act_unique` AND `act_time`>=1382511600 AND `act_time`<1382515200
GROUP BY `visit_client`
And the info about the tables involved:
track_visits_store STATUS:
Name: track_visits_store
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 44791389
Avg_row_length: 41
Data_length: 1836446949
Max_data_length: 11540474045136895
Index_length: 1708906496
Data_free: 0
Auto_increment: NULL
Collation: latin1_general_ci
track_visits_store CREATE:
CREATE TABLE `track_visits_store` (
`visit_client` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_unique` bigint(20) unsigned NOT NULL,
`visit_ip` int(10) unsigned NOT NULL DEFAULT '0',
`visit_actions` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_land` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
`visit_traffic_extra1` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_extra2` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_keyword` int(10) unsigned NOT NULL DEFAULT '0',
`visit_page` int(10) unsigned NOT NULL DEFAULT '0',
`visit_country` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_bot` tinyint(1) unsigned NOT NULL DEFAULT '0',
KEY `visit_client` (`visit_client`),
KEY `visit_unique` (`visit_unique`),
KEY `visit_land` (`visit_land`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
***************************
track_acts_store STATUS:
Name: track_acts_store
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 159676779
Avg_row_length: 25
Data_length: 3991919475
Max_data_length: 7036874417766399
Index_length: 6656314368
Data_free: 0
Auto_increment: 159676780
Collation: latin1_general_ci
track_acts_store CREATE:
CREATE TABLE `track_acts_store` (
`sid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`act_unique` bigint(20) unsigned NOT NULL,
`act_time` int(10) unsigned NOT NULL DEFAULT '0',
`act_page` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`sid`),
KEY `page_unique` (`act_unique`),
KEY `act_time` (`act_time`)
) ENGINE=MyISAM AUTO_INCREMENT=159676780 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
And this is my server configuration:
--datadir=/var/lib/mysql
--tmpdir=/dev/shm
--socket=/var/lib/mysql/mysql.sock
--skip-locking
--table_cache=4096
--table_definition_cache=4096
--thread_cache_size=150
--back_log=2048
--max_connect_errors=99999
--open-files-limit=65535
--interactive_timeout=30
--wait_timeout=30
--max_connections=500
--max_user_connections=520
--log-slow-queries=mysql-slow.log
--long_query_time=2
--log-queries-not-using-indexes
--max_allowed_packet=16M
--tmp_table_size=64M
--max_heap_table_size=64M
--query_cache_size=0
--query_cache_min_res_unit=1536
--query_cache_type=0
--sort_buffer_size=1M
--read_buffer_size=1M
--read_rnd_buffer_size=8M
--join_buffer_size=10M
--key_buffer_size=3072M
--myisam_sort_buffer_size=64M
--innodb_log_file_size=100M
--innodb_buffer_pool_size=16M
This is the MySQL configuration:
--datadir=/var/lib/mysql
--tmpdir=/dev/shm
--socket=/var/lib/mysql/mysql.sock
--skip-locking
--table_cache=4096
--table_definition_cache=4096
--thread_cache_size=150
--back_log=2048
--max_connect_errors=99999
--open-files-limit=65535
--interactive_timeout=30
--wait_timeout=30
--max_connections=500
--max_user_connections=520
--log-slow-queries=mysql-slow.log
--long_query_time=2
--log-queries-not-using-indexes
--max_allowed_packet=16M
--tmp_table_size=64M
--max_heap_table_size=64M
--query_cache_size=0
--query_cache_min_res_unit=1536
--query_cache_type=0
--sort_buffer_size=1M
--read_buffer_size=1M
--read_rnd_buffer_size=8M
--join_buffer_size=10M
--key_buffer_size=3072M
--myisam_sort_buffer_size=64M
--innodb_log_file_size=100M
--innodb_buffer_pool_size=16M
Is there any way to reduce the time for this query?
Thanks in advance,
Gustavo Mateos,
SkyYield,
Madrid, Spain