CODEWITHSUNDEEP
mysqlsqldatabaseoptimizationfilesort
camgt
camgt

Reputation: 13

How to optimize my MySQL query with an index

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

Upvotes: 0

Views: 138

Answers (3)

Rick James
Rick James

Reputation: 142296

That may have been a good example 10-15 years ago, but times have changed.

Use InnoDB instead of MyISAM. Consider using utf8 instead of latin1. Add an index on titles (to_date). Get rid of the redundant index on only (emp_no) in both tables. Add INDEX(title).

(I agree that you ought to use the newer JOIN syntax.)

And, the AVG is likely to be wrong without converting to Gordon's subquery version. (That construct probably did not exist when the sample was developed.)

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

You should use explicit join syntax, rather than implicit. That won't help performance though. What would help, is capturing the result of the function NOW() in a variable, that way it only has to be evaluated once, instead of twice per row:

DECLARE @dtNOW DATETIME = NOW()

SELECT     t.title
,          AVG(s.salary) salario_medio
FROM       titles t
INNER JOIN salaries s
        ON t.emp_no = s.emp_no
WHERE      t.to_date > dtNOW
       AND s.to_date > dtNOW
GROUP BY   t.title
ORDER BY   salario_medio DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 1

Related Questions