MySQL: Inner Join with Group By is creating a temporary table (doing a table scan)

Question

I've created a query which produces the correct results, yet it is doing a full table scan and creating a temporary table. (Using temporary; Using filesort).

I'd like it to use the indexes, but because I'm using group by to remove duplicates it does not use the indexes which are available. I've tried removing Group By and using Distinct, but this does not solve the problem.

I'm using a many-to-many relationship because an employee can have multiple positions and multiple employees can have the same positions.

Tables:

    CREATE TABLE IF NOT EXISTS `Employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) DEFAULT NULL,
  `middlename` varchar(7) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `firstname` (`firstname`),
  KEY `middlename` (`middlename`),
  KEY `lastname` (`lastname`),
  KEY `city` (`city`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `Employee_Position` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `position_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  KEY `function_id` (`position_id`),
  KEY `employee_id+position_id` (`employee_id`,`position_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `Positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Query:

SELECT 
    e.id, 
    e.firstname, 
    e.middlename, 
    e.lastname, 
    e.city 
FROM Employees AS e 
INNER JOIN Employee_Position AS ep 
    ON e.id = ep.employee_id 
WHERE (ep.position_id IN (1, 2, 3, 4)) 
GROUP BY e.id 
ORDER BY e.lastname

Why is this creating a temporary table and not using indexes? And is there any way to fix this?

Answer 1

Your query is doing an aggregation unnecessarily.

Here is a better way to phrase the query:

SELECT e.id, e.firstname, e.middlename, e.lastname, e.city
FROM Employees e 
WHERE EXISTS (SELECT 1
              FROM Employee_Position ep 
              WHERE e.id = ep.employee_id AND ep.position_id IN (1, 2, 3, 4)
             )
ORDER BY e.lastname;

You already have the right index for this query, on employee_position(employee_id, position_id).