Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
SSMUSOKE's answer may help, but not necessarily. How much data is in the tables being pulled from? Are they indexed well? There could be a lot of potential bottle-necks in something like this. –  Wes Crockett Dec 21 '12 at 15:48
add comment

1 Answer

up vote 0 down vote accepted

Why not use a join condition instead of an subquery

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'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.