MySQL -优化查询

Question

我不知道如何进一步优化这个查询

SELECT  COUNT(*)
    FROM  `job_offer` AS `o`
    WHERE  o.`status` = 1
      AND  EXISTS
      ( SELECT  1
            FROM  `job_offer_localitymap` AS lm
            WHERE  lm.`job_offerid` = o.`job_offerid`
              AND  lm.`gps_localityid` IN (116, 1) 
      ) 

在这里解释为图片：http://postimg.org/image/52wd06f3f/full/

它是使用子查询的简单select，它在localhost上运行大约52毫秒，这太多了。还有更多的记录服务器，它在那里运行350毫秒。我还尝试将此查询替换为inner join和group by，结果相同。

    SELECT SQL_NO_CACHE COUNT(DISTINCT(o.job_offerid)) 
    FROM `job_offer` AS `o` 
    INNER JOIN `job_offer_localitymap` AS lm ON lm.job_offerid = o.`job_offerid` 
        AND lm.`gps_localityid` IN (116, 1)
    WHERE (o.`status` = 1)

在这里解释为图片：http://postimg.org/image/3renvl3gx/full/

我就是不明白为什么会有这样的问题。

统计数据: count返回1415行，job_offer返回65k行，job_offer_locality_map返回100k行。所有表格都是MyISAM格式的。

谢谢你的建议。

下面是数据库转储(表job_offer有很多行-大约50-60行，所以我在这里给出一个简单的版本)

DROP TABLE IF EXISTS `job_offer`;
CREATE TABLE `job_offer` (
  `job_offerid` int(13) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `status` int(13) NOT NULL DEFAULT '1',
  `sequence` int(13) NOT NULL DEFAULT '0',
  `sequence_desc` tinyint(1) DEFAULT NULL,
  `active_updated_when` datetime DEFAULT NULL,
  PRIMARY KEY (`job_offerid`),
  KEY `active_updated_when` (`active_updated_when`),
  KEY `status_sequence_desc_active_updated_when_job_offerid` (`status`,`sequence_desc`,`active_updated_when`,`job_offerid`),
  KEY `status_job_offerid` (`status`,`job_offerid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `job_offer_localitymap`;
CREATE TABLE `job_offer_localitymap` (
  `job_offer_localitymapid` int(13) NOT NULL AUTO_INCREMENT,
  `old_id` int(13) NOT NULL DEFAULT '0',
  `gps_localityid` int(13) NOT NULL,
  `job_offerid` int(13) NOT NULL,
  `sequence` int(13) NOT NULL DEFAULT '100',
  `created` datetime NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `old_localityid` int(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`job_offer_localitymapid`),
  KEY `job_offerid_gps_localityid` (`job_offerid`,`gps_localityid`),
  KEY `job_offerid` (`job_offerid`),
  KEY `gps_localityid` (`gps_localityid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

INDEX(job_offered, gps_localityid)

向我们展示JOIN版本及其EXPLAIN和SHOW CREATE TABLE