Início / dba / Perguntas / 30799
Accepted
ashuthosh
Asked: 2012-12-20 21:13:41 +0800 CST

Consulta de longa duração

  • 772

Esta é a minha consulta de longa duração:

select *
from t_keyword
where id in
(
    select rel.element_id 
    from t_event event
    join t_event_element_rel rel on 
        event.id = rel.event_id
    where
        event.domain_id=182
        and event.event_type_cd = 26
        and event.event_create_date = '2012-12-18'
)

Mesa:

 mysql> show create table t_keyword\G
*************************** 1. row ***************************
       Table: t_keyword
Create Table: CREATE TABLE `t_keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword_name` varchar(255) DEFAULT NULL,
  `keyword_value` varchar(255) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `description` varchar(2000) DEFAULT NULL,
  `own_domain_id` int(11) DEFAULT NULL,
  `rank_check` int(11) DEFAULT NULL,
  `rank1` int(11) DEFAULT NULL COMMENT 'yesterday rank value',
  `rank2` int(11) DEFAULT NULL COMMENT 'the day before yesterday rank value',
  `rank3` int(11) DEFAULT NULL COMMENT 'special date rank for overstock.com',
  `yesterday_entrances` int(11) DEFAULT NULL COMMENT 'yesterday entrances',
  `week_entrances` int(11) DEFAULT NULL COMMENT '7 days entrances',
  `current_ctr` float(16,4) DEFAULT NULL COMMENT 'Current CTR',
  `monthly_search_volume` int(11) DEFAULT NULL COMMENT 'Most Recent Month search volume',
  `avg_monthly_search_volume` int(11) DEFAULT NULL COMMENT 'avg_monthly_search_volume',
  `traffic_increase` int(11) DEFAULT NULL COMMENT 'Traffic Increase',
  `rank_improvement` int(11) DEFAULT NULL COMMENT 'Rank Improvement',
  `rank_update_date` date DEFAULT NULL COMMENT 'rank be updated for Special Date',
  `top_rank_targeturl_id` int(11) DEFAULT NULL,
  `frequency` int(10) DEFAULT '1' COMMENT '1: daily, 2: weekly, 3: monthly',
  `score` float DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `bing_rank1` int(10) DEFAULT NULL,
  `bing_rank2` int(10) DEFAULT NULL,
  `yesterday_bing_entrances` int(11) DEFAULT NULL,
  `bing_rank_improvement` int(11) DEFAULT NULL,
  KEY `id` (`id`),
  KEY `keyword_name` (`keyword_name`),
  KEY `own_domain_id` (`own_domain_id`,`rank_check`),
  KEY `rank_check` (`rank_check`)
) ENGINE=InnoDB AUTO_INCREMENT=641229869 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`rank_check`)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION pEOW VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.40 sec)


mysql> show create table t_event_element_rel\G
*************************** 1. row ***************************
       Table: t_event_element_rel
Create Table: CREATE TABLE `t_event_element_rel` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `element_type_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  `element_desc` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_event` (`event_id`),
  KEY `FK_eventType` (`element_type_id`),
  KEY `element_type_id_element_id` (`element_type_id`,`element_id`)
) ENGINE=MyISAM AUTO_INCREMENT=41241643 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql performance

1 respostas

  1. Best Answer

    Por que não usar uma condição de junção em vez de uma subconsulta

    SELECT * from t_keyword 
INNER JOIN t_event event ON (t_keyword.id = rel.element_id) 
INNER JOIN t_event_element_rel rel ON event.id = rel.event_id 
WHERE event.domain_id=182 and event.event_type_cd=26 and event.event_create_date='2012-12-18'
    • 0

relate perguntas