MySQL Forums
Forum List  »  Optimizer & Parser

help me, optimize a long running query
Posted by: h ch
Date: October 25, 2011 12:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
help me, optimize a long running query
2649
October 25, 2011 12:00AM
987
October 27, 2011 12:39AM
1331
December 26, 2011 11:52PM


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.