mysql - 优化缓慢的 MySQL 查询以删除 Using where；使用临时的；使用文件排序

Question

我正在尝试优化一个查询，该查询需要超过 2 秒的时间才能针对仅包含 10,000 条记录的数据库返回响应。 Explain 显示它应该几乎立即返回。我假设性能低下是因为 Using where;使用临时的；使用文件排序？

这是查询:

  SELECT distinct a.id, profi.company, profi.logo, profi.id as profileid ,  a.job_detail_section as job_detail_section_tmp ,  a.title as title_tmp ,  a.location_id as location_id_tmp ,  a.salary_min as salary_min_tmp ,  a.salary_max as salary_max_tmp ,  a.currency as currency_tmp ,  a.frequency as frequency_tmp ,  a.job_type as job_type_tmp ,  a.cat_id as cat_id_tmp ,  a.job_title as job_title_tmp ,  a.job_detail as job_detail_tmp ,  a.status as status_tmp ,  a.created_date as created_date_tmp ,  a.effected_date as effected_date_tmp ,  a.is_hotjob as is_hotjob_tmp ,  a.user_id as user_id_tmp ,  a.id as id_tmp ,  a.views as views_tmp ,  a.ordering as ordering_tmp ,  a.apply_type as apply_type_tmp ,  a.direct_url as direct_url_tmp 
  FROM jos_ja_jobs as a
  INNER JOIN jos_ja_profiles as  profi   ON profi.user_id=a.user_id AND profi.approved=1
  INNER JOIN jos_users as rs  ON rs.id=a.user_id AND rs.block=0
  WHERE a.status='Approved'  AND (a.effected_date<=now()) AND ( (DATE_ADD(a.effected_date, INTERVAL 90 DAY) >= now()  AND a.is_hotjob=0) or (DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now()  AND a.is_hotjob=1) )
  ORDER BY a.is_hotjob desc, a.effected_date desc LIMIT 0, 5

和解释:

    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
    | id | select_type | table | type   | possible_keys                | key     | key_len | ref                    | rows | Extra                                        |
    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | a     | ref    | effected_date,user_id,status | status  | 768     | const                  | 1880 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | profi | ref    | user_id,approved             | user_id | 5       | esljw_joomla.a.user_id |    1 | Using where                                  |
    |  1 | SIMPLE      | rs    | eq_ref | PRIMARY                      | PRIMARY | 4       | esljw_joomla.a.user_id |    1 | Using where; Distinct                        |
    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

和表结构:

CREATE TABLE IF NOT EXISTS `jos_ja_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `salary_range` varchar(255) NOT NULL,
  `location_id` varchar(255) NOT NULL,
  `job_detail` mediumtext,
  `status` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `salary_min` int(11) DEFAULT NULL,
  `salary_max` int(11) DEFAULT NULL,
  `effected_date` datetime DEFAULT NULL,
  `server_effected_date` datetime DEFAULT NULL,
  `image` varchar(100) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `checked_out` tinyint(4) DEFAULT NULL,
  `checked_out_time` date DEFAULT NULL,
  `cat_id` varchar(255) NOT NULL DEFAULT '0',
  `job_code` varchar(255) DEFAULT NULL,
  `section2` varchar(255) DEFAULT NULL,
  `section3` varchar(255) DEFAULT NULL,
  `section4` varchar(255) DEFAULT NULL,
  `is_hotjob` tinyint(4) DEFAULT '0',
  `user_id` int(11) DEFAULT NULL,
  `job_type` varchar(255) DEFAULT NULL,
  `views` int(11) DEFAULT '0',
  `ordering` int(11) DEFAULT '0',
  `feed_id` int(11) DEFAULT '0',
  `feed_guid` varchar(1000) DEFAULT NULL,
  `import_date` datetime DEFAULT NULL,
  `apply_type` varchar(255) DEFAULT '3',
  `direct_url` varchar(255) DEFAULT '',
  `distance` varchar(255) DEFAULT '0',
  `zipcode` int(11) DEFAULT NULL,
  `job_detail_section` varchar(255) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `job_duration` int(11) DEFAULT '30' COMMENT 'Number of available days for job',
  `frequency` varchar(255) NOT NULL DEFAULT '1',
  `class_type` varchar(255) DEFAULT NULL,
  `job_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `effected_date` (`effected_date`),
  KEY `job_type` (`job_type`),
  KEY `location_id` (`location_id`),
  KEY `user_id` (`user_id`),
  KEY `created_date` (`created_date`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10124 ;

CREATE TABLE IF NOT EXISTS `jos_ja_profiles` (
  `id` mediumint(11) NOT NULL AUTO_INCREMENT,
  `company` varchar(255) NOT NULL,
  `business_type` varchar(255) DEFAULT '1',
  `logo` varchar(255) DEFAULT NULL,
  `section2` varchar(255) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `tel` varchar(255) DEFAULT NULL,
  `section3` varchar(255) DEFAULT NULL,
  `checked_out` varchar(255) DEFAULT NULL,
  `checked_out_time` datetime DEFAULT NULL,
  `location_id` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `cat_id` varchar(255) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `short_desc` text,
  `approved` tinyint(4) NOT NULL DEFAULT '0',
  `subscription_section` varchar(255) DEFAULT '',
  `duration_notice` varchar(255) DEFAULT '',
  `latest_sendmail` date DEFAULT NULL,
  `modified_notice` tinyint(4) NOT NULL DEFAULT '0',
  `change_log` longtext,
  `profile_detail_section` varchar(255) DEFAULT NULL,
  `time_offset` varchar(255) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `approved` (`approved`),
  KEY `company` (`company`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=852 ;

CREATE TABLE IF NOT EXISTS `jos_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `username` varchar(150) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `usertype` varchar(25) NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `sendEmail` tinyint(4) DEFAULT '0',
  `gid` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` varchar(100) NOT NULL DEFAULT '',
  `params` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usertype` (`usertype`),
  KEY `idx_name` (`name`),
  KEY `gid_block` (`gid`,`block`),
  KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `registerDate` (`registerDate`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9095 ;

感谢任何帮助!

编辑:能够通过添加此索引来加快速度:

ALTER TABLE jos_ja_jobs ADD INDEX hot_date (is_hotjob, effected_date);

Answer 1

您应该在要过滤的任何字段上都有索引。尝试在工作表中的日期字段上添加索引。

好吧，不是针对每一个，而是针对那些会大大缩小您的结果范围或者您将经常查询的那些。