Optimize slow MySQL query to remove Using where; Using temporary; Using filesort

I'm trying to optimize a query which is taking over 2 seconds to return a response against a database of only 10,000 records. Explain shows it should return almost instantly. I assume the slow performance is because of Using where; Using temporary; Using filesort?

Here's the query:

  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

And the explain:

    +----+-------------+-------+--------+------------------------------+---------+---------+------------------------+------+----------------------------------------------+
    | 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)

And the table structure:

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 ;

Appreciate any help!

Edit: Was able to make this much faster by adding this index:

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

You should have indexes on any field you will filter on. Try adding an index on your date fields in the jobs table.

Well, not on every one, but on ones that will narrow down your results a lot or that you will be querying on often.

If you REALLY want to optimize that query, put actual values to the date instead of using functions now() and some of the other date functions. Also, make sure that all your inner joins are being used. For instance:

    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 
  INNER JOIN jos_users as rs  ON rs.id=a.user_id and rs.id = profi.user_id
  WHERE a.status='Approved'  
  AND profi.approved=1
  AND rs.block=0
  AND a.effected_date <= '2011-06-21'
  -- Instead have the two dates place there by the application
  AND( 
  (a.effected_date BETWEEN '2011-06-21' AND '2011-09-21' AND a.is_hotjob=0) 
  OR (a.effected_date BETWEEN '2011-06-21' AND '2011-07-21' AND a.is_hotjob=1) )
  ORDER BY a.is_hotjob desc, a.effected_date desc LIMIT 0, 5

Let me know if that helps!

Regards, Marcelo

Like your other query help post, and my answer about using the "STRAIGHT_JOIN" clause, do the same here...

SELECT STRAIGHT_JOIN DISTINCT ...

Additionally, have a compound index on (Status, Effected_Date)

Finally, in your where clause, add a fixed criteria on the effected_Date to be ANY Effected Date of at least 90 days... Since that is the furthest back you will allow based on hot job or not, at least the status and date filter will match the index and be optimized... Leave the rest of your OTHER date criteria as-is as they look good and will ultimately filter down even further to the final results you expect...

WHERE
       a.status='Approved'  
   AND a.effected_date >= date_sub(now(), interval 90 days)
   AND (rest of your other date criteria)