i have a query which take a long time to run,any one can help optimize?
100 rows in set (13 min 31.77 sec)
mysql> explain select t.id,t.poster,t.pubDate,t.company,t.status,t.product,t.price,t.units,t.intro,tt.cate AS type_name
from trade_lead.trade AS t,trade_lead.trade_type AS tt,trade_lead.trade_ft as ft
where 1 and t.pubDate >= date_format(date_add(now(),interval -7 day),'%Y-%m-%d') and t.type = tt.cat_id and t.id = ft.id ORDER BY ft.s_order desc,ft.id DESC limit 0,100;
+----+-------------+-------+--------+----------------------+---------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+---------+---------+-------------------+--------+----------------------------------------------+
| 1 | SIMPLE | t | range | PRIMARY,pubDate,type | pubDate | 4 | NULL | 585401 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | tt | ref | cat_id | cat_id | 5 | trade_lead.t.type | 1 | Using where |
| 1 | SIMPLE | ft | eq_ref | PRIMARY | PRIMARY | 4 | trade_lead.t.id | 1 | |
+----+-------------+-------+--------+----------------------+---------+---------+-------------------+--------+----------------------------------------------+
mysql> show create table trade\G
*************************** 1. row ***************************
Table: trade
Create Table: CREATE TABLE `trade` (
`id` int(11) NOT NULL auto_increment,
`sid` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`poster` varchar(20) collate utf8_unicode_ci default NULL,
`editor` varchar(20) collate utf8_unicode_ci default NULL,
`edit_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`post_date` timestamp NOT NULL default '0000-00-00 00:00:00',
`pubDate` date default NULL,
`webSite` varchar(60) collate utf8_unicode_ci default NULL,
`http` varchar(100) collate utf8_unicode_ci default NULL,
`status` int(4) NOT NULL default '0'
`rank` int(11) default '9950',
`quality` int(11) default '0',
`cate` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`cate1` varchar(50) collate utf8_unicode_ci default NULL,
`cate2` varchar(50) collate utf8_unicode_ci default NULL,
`cate4` varchar(50) collate utf8_unicode_ci default NULL,
`cate6` varchar(50) collate utf8_unicode_ci default NULL,
`cate7` varchar(50) collate utf8_unicode_ci default NULL,
`tag` varchar(150) collate utf8_unicode_ci default NULL,
`type` varchar(4) collate utf8_unicode_ci NOT NULL,
`title` varchar(150) collate utf8_unicode_ci default NULL,
`product` varchar(100) collate utf8_unicode_ci default NULL,
`product_en` varchar(100) collate utf8_unicode_ci default NULL,
`cas_no` varchar(20) collate utf8_unicode_ci default NULL,
`spec` text collate utf8_unicode_ci,
`link` varchar(255) collate utf8_unicode_ci default NULL,
`category` varchar(20) collate utf8_unicode_ci default NULL,
`subcategory` varchar(20) collate utf8_unicode_ci default NULL,
`region` varchar(20) collate utf8_unicode_ci default NULL,
`expiryDate` int(11) default '30',
`price` varchar(200) collate utf8_unicode_ci default NULL,
`units` varchar(10) collate utf8_unicode_ci default NULL,
`packing` varchar(100) collate utf8_unicode_ci default NULL,
`produce_area` varchar(100) collate utf8_unicode_ci default NULL,
`provide_area` varchar(100) collate utf8_unicode_ci default NULL,
`brand` varchar(100) collate utf8_unicode_ci default NULL,
`company` varchar(100) collate utf8_unicode_ci default NULL,
`property` varchar(20) collate utf8_unicode_ci default NULL,
`state` varchar(20) collate utf8_unicode_ci default NULL,
`intro` text collate utf8_unicode_ci,
`contact` varchar(50) collate utf8_unicode_ci default NULL,
`tel` varchar(50) collate utf8_unicode_ci default NULL,
`fax` varchar(50) collate utf8_unicode_ci default NULL,
`zip` varchar(20) collate utf8_unicode_ci default NULL,
`mobile` varchar(50) collate utf8_unicode_ci default NULL,
`email` varchar(50) collate utf8_unicode_ci default NULL,
`address` varchar(100) collate utf8_unicode_ci default NULL,
`company_http` varchar(50) collate utf8_unicode_ci default NULL,
`keywords` varchar(100) collate utf8_unicode_ci default NULL,
`pic_name` varchar(20) collate utf8_unicode_ci default NULL,
`pic_name1` varchar(100) collate utf8_unicode_ci default NULL,
`image` varchar(200) collate utf8_unicode_ci default NULL,
`data_flag` tinyint(4) default '0',
`ip_address` varchar(20) collate utf8_unicode_ci default NULL,
`additional` text collate utf8_unicode_ci,
`channel` varchar(20) collate utf8_unicode_ci default NULL,
`hobby` varchar(25) collate utf8_unicode_ci default NULL,
`tosmartmail` int(2) default NULL,
`tomail_date` varchar(30) collate utf8_unicode_ci default NULL,
`trade_flag` varchar(20) collate utf8_unicode_ci default NULL,
`old_id` int(11) default NULL,
`find_flag` int(4) default '0',
`do_again_flag` smallint(2) NOT NULL default '0'
PRIMARY KEY (`id`),
UNIQUE KEY `sid` (`sid`),
KEY `category` (`category`),
KEY `pubDate` (`pubDate`),
KEY `poster` (`poster`),
KEY `product` (`product`),
KEY `post_date` (`post_date`),
KEY `type` (`type`,`category`),
KEY `state` (`state`),
KEY `price` (`price`),
KEY `idx_pubDate_status_id` (`pubDate`,`status`,`id`),
KEY `idx_type_id` (`type`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10233169 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
mysql> show create table trade_type\G
*************************** 1. row ***************************
Table: trade_type
Create Table: CREATE TABLE `trade_type` (
`id` int(11) NOT NULL auto_increment,
`cate` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`dir` varchar(20) collate utf8_unicode_ci default NULL,
`cat_id` int(5) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cat_id` (`cat_id`),
KEY `cate` (`cate`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
mysql> show create table trade_ft\G
*************************** 1. row ***************************
Table: trade_ft
Create Table: CREATE TABLE `trade_ft` (
`id` int(11) NOT NULL
`ft` text NOT NULL
`s_order` char(15) default NULL
`ftime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '¸üÐÂʱ¼ä',
PRIMARY KEY (`id`),
FULLTEXT KEY `ft` (`ft`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> show table status like 'trade'\G
*************************** 1. row ***************************
Name: trade
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1791634
Avg_row_length: 2527
Data_length: 4528650524
Max_data_length: 281474976710655
Index_length: 283739136
Data_free: 0
Auto_increment: 10233169
Create_time: 2011-10-22 11:28:13
Update_time: 2011-10-22 11:33:43
Check_time: 2011-10-22 11:54:10
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show table status like 'trade_type'\G
*************************** 1. row ***************************
Name: trade_type
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 20
Avg_row_length: 20
Data_length: 400
Max_data_length: 281474976710655
Index_length: 4096
Data_free: 0
Auto_increment: 21
Create_time: 2011-10-21 14:39:18
Update_time: 2011-10-21 14:39:18
Check_time: 2011-10-21 14:39:18
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show table status like 'trade_ft'\G
*************************** 1. row ***************************
Name: trade_ft
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1810256
Avg_row_length: 76
Data_length: 138326364
Max_data_length: 281474976710655
Index_length: 122781696
Data_free: 0
Auto_increment: NULL
Create_time: 2011-10-21 15:00:04
Update_time: 2011-10-21 15:01:32
Check_time: 2011-10-21 15:02:31
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Edited 1 time(s). Last edit at 10/26/2011 10:45PM by h ch.