This is my long running query:
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'
)
Table:
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)
Best Answer
Why not use a join condition instead of an subquery