How to optimize my MySQL query with an index

Question

On this database (employees from MySQL samples) I have to optimize this query using index:

SELECT t.title,
       Avg(s.salary) salario_medio
FROM   titles t,
       salaries s
WHERE  t.emp_no = s.emp_no
       AND t.to_date > Now()
       AND s.to_date > Now()
GROUP  BY t.title
ORDER  BY salario_medio DESC;  

I have already create this index on the "salaries" table:

CREATE INDEX to_date_idx ON salaries(to_date);

But the EXPLAIN gives me those rows:

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: s
    type: range
    possible_keys:PRIMARY,emp_no,to_date_idx
    key: to_date_idx
    key_len: 3
    ref: NULL
    rows: 370722
    Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: t
    type: ref
    possible_keys: PRIMARY,emp_no
    key: emp_no
    key_len: 4
    ref: employees.s.emp_no
    rows: 1
    Extra: Using where

I would like not to use Using temporary and Using filesort.

INFO:

SHOW CREATE TABLE salaries;

CREATE TABLE `salaries`
  (
     `emp_no`    INT(11) NOT NULL,
     `salary`    INT(11) NOT NULL,
     `from_date` DATE NOT NULL,
     `to_date`   DATE NOT NULL,
     PRIMARY KEY (`emp_no`, `from_date`),
     KEY `emp_no` (`emp_no`),
     KEY `to_date_idx` (`to_date`)
  ) engine=myisam DEFAULT charset=latin1 show CREATE TABLE titles;

CREATE TABLE `titles`
  (
     `emp_no`    INT(11) NOT NULL,
     `title`     VARCHAR(50) NOT NULL,
     `from_date` DATE NOT NULL,
     `to_date`   DATE DEFAULT NULL,
     PRIMARY KEY (`emp_no`, `title`, `from_date`),
     KEY `emp_no` (`emp_no`)
  )
engine=myisam
DEFAULT charset=latin1  

Answer 1

I would suggest writing the query like this:

SELECT t.title,
       (SELECT AVG(s.salary)
        FROM salaries s
        WHERE t.emp_no = s.emp_no AND 
              s.to_date > NOW()
       ) as salario_medio
FROM titles t
WHERE t.to_date > NOW()
ORDER BY salario_medio DESC;

This query can take advantage of indexes on titles(to_date, title, emp_no) and salaries(emp_no, to_date).

This eliminates the sorting needed for the aggregation. The query still needs to sort the final results.