title: Optimize MySQL join query to remove Using temporary and use an index?
body: <p>I have a query with <code>ORDER BY name</code> and the index on <code>name</code> is being ignored.  </p>
  
  <p>How can I optimize the query to use an index and get rid of <code>Using temporary</code> from EXPLAIN?  </p>
  
  <p>I have <code>log-queries-not-using-indexes</code> enabled and I'm seeing this query thousands of times.</p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT l.parent_id, j.id, j.location_id, j.currency, j.frequency, ROUND((j.salary_min + j.salary_max)/2) as salary 
  FROM jobs AS j
  JOIN location AS l
      ON j.location_id = l.id
  WHERE j.salary_min !=0 
      AND j.status != 'Rejected'      
      AND l.published =1
      AND date_sub(now(), interval 1 month) &lt;= j.effected_date
  ORDER BY l.name
  </code></pre>
  
  <p>The explain:</p>
  
  <pre><code>+----+-------------+-------+--------+----------------------------------+---------------+---------+----------------------------+------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                    | key           | key_len | ref                        | rows | Extra                                        |
  +----+-------------+-------+--------+----------------------------------+---------------+---------+----------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | j     | range  | effected_date,location_id,status | effected_date | 9       | NULL                       |  562 | Using where; Using temporary; Using filesort |
  |  1 | SIMPLE      | l     | eq_ref | PRIMARY                          | PRIMARY       | 4       | esljw_joomla.j.location_id |    1 | Using where                                  |
  +----+-------------+-------+--------+----------------------------------+---------------+---------+----------------------------+------+----------------------------------------------+
  2 rows in set (0.01 sec)
  </code></pre>
  
  <p>And the table structure:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `jobs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `location_id` varchar(255) NOT NULL,
    `status` varchar(255) DEFAULT NULL,
    `currency` varchar(255) DEFAULT NULL,
    `salary_min` int(11) DEFAULT NULL,
    `salary_max` int(11) DEFAULT NULL,
    `effected_date` datetime DEFAULT NULL,
    `frequency` varchar(255) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    KEY `effected_date` (`effected_date`),
    KEY `location_id` (`location_id`),
    KEY `status` (`status`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10130 ;
  
  CREATE TABLE IF NOT EXISTS `location` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(128) DEFAULT NULL,
    `parent_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=304 ;
  </code></pre>