Normalization and fight against “using temporary; using filesort"

Question

MySQL

Database of employees.

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `surname` varchar(150) NOT NULL,
  `city_id` int(3) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `last_vacation_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Последнее время отпуска (timestamp)',
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`,`last_vacation_time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Таблица с возрастами детей работников
--

CREATE TABLE IF NOT EXISTS `employee_child_age` (
  `employee_id` int(11) unsigned NOT NULL,
  `child_age` int(3) unsigned NOT NULL DEFAULT '0',
  KEY `employee_id` (`employee_id`,`child_age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Таблица с городами
--

CREATE TABLE IF NOT EXISTS `employee_city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(100) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=44 ;

-- --------------------------------------------------------

--
-- Таблица с различным пометками к работникам
--

CREATE TABLE IF NOT EXISTS `employee_mark` (
  `employee_id` int(11) unsigned NOT NULL,
  `mark_name` varchar(15) CHARACTER SET utf8 NOT NULL,
  KEY `employee_id` (`employee_id`,`mark_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Suppose you need to select employees whose children are older than 10 years. So you need to show the city where the employee is located and sort the data by the time of the last vacation

SELECT e.*, city.* FROM employee AS e 
    INNER JOIN employee_child_age AS age ON age.employee_id=e.id AND age.child_age>10 
    INNER JOIN employee_city AS city ON city.city_id=e.city_id 
    WHERE e.deleted=0 ORDER BY e.last_vacation_time;

EXPLAIN

+----+-------------+-------+------+-------------- -+-------------+---------+-----------+------+----- -----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+----+-----+-------+------+--------------- +-------------+---------+-----------+------+------ ----------------------------------------+
| 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; using temporary; Using filessort |
| 1 | SIMPLE | age | ref | employee_id | employee_id | 4 | test.e.id | 1 | Using where; using index |
| 1 | SIMPLE | city ​​| ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+-----+-------+------+--------------- +-------------+---------+-----------+------+------ ----------------------------------------+

Or the same requirements but only need to select workers with a certain mark

    SELECT e.*, city.* FROM employee AS e 
    INNER JOIN employee_child_age AS age ON age.employee_id=e.id AND age.child_age>10 
    INNER JOIN `employee_mark` AS mark ON mark.employee_id=e.id AND mark.mark_name='frg' 
    INNER JOIN employee_city AS city ON city.city_id=e.city_id 
    WHERE e.deleted=0 ORDER BY e.last_vacation_time;

EXPLAIN

+----+-------------+-------+------+-------------- ---+-------------+---------+-----------------+---- --+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+----+-----+-------+------+--------------- --+-------------+---------+-----------------+----- -+---------------------------------+
| 1 | SIMPLE | e | ref | PRIMARY,deleted | deleted | 1 | const | 2 | using temporary; Using filessort |
| 1 | SIMPLE | age | ref | employee_id | employee_id | 4 | test.e.id | 1 | Using where; using index |
| 1 | SIMPLE | city ​​| ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | mark | ref | employee_id | employee_id | 51 | test.e.id,const | 2 | Using where; using index |
+----+-----+-------+------+--------------- --+-------------+---------+-----------------+----- --+--------------------------------+

Tried different types of indexes but from Using temporary; Using filesort failed to get rid of. Can it be normalized in some other way?

Answer 1

Definitely, you need to create indexes on secondary keys in all tables (just in case, let me remind you that a secondary key is a field that is used in JOIN), as well as an index on last_vacation_time in the employee table - there is simply nothing to optimize without these indexes. An index on the fields on which the selection takes place will also help.
To begin with, it is worth trying to remove AND from the JOIN condition and move it to the WHERE block - there is only one sense, but the MySQL optimizer has never been smart and quick-witted.
It is also worth trying to use one ENGINE for all tables.
As a last resort, I personally would play with the order of the JOINs. I DO NOT know if this order affects the query plan in MySQL, but I heard something bad. Perhaps this feature was fixed in the latest version.
- By the way, about the data scheme. I would make a dictionary of label types (names) so as not to duplicate string information. And vacations would store everything in a separate table, not just the last one. But first, of course, you need to learn how to build fast queries.