Optimizing a 3 table join query

Question

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
job.short_description, job.status, job.expires_at, job.nb_vacancies, 
job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
COUNT(fk_job) AS `application_count` 
FROM `job` 
INNER JOIN `company` ON (job.fk_company=company.id_company) 
LEFT JOIN `job_application` ON (job.id_job=job_application.fk_job) 
WHERE job.deleted_at IS NULL  
GROUP BY job.id_job 
ORDER BY job.id_job DESC 
LIMIT 50

This query takes quite a while, although all of the fields that we use on the JOIN, GROUP BY and ORDER BY statements are either indexed, or declared as foreign key or primary key.

+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table           | type  | possible_keys        | key                  | key_len | ref                        | rows | Extra                                        |
+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | company         | index | PRIMARY              | company_I_2          | 4       | NULL                       | 3244 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | job             | ref   | job_FI_1             | job_FI_1             | 4       | intjobs.company.id_company |    3 | Using where                                  |
|  1 | SIMPLE      | job_application | ref   | job_application_FI_2 | job_application_FI_2 | 4       | intjobs.job.id_job         |    1 | Using index                                  |
+----+-------------+-----------------+-------+----------------------+----------------------+---------+----------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

SHOW CREATE TABLE job;

CREATE TABLE `job` (
  `id_job` int(11) NOT NULL AUTO_INCREMENT,
  `fk_company` int(11) NOT NULL,
  `fk_user` int(11) NOT NULL,
  `fk_job_category` int(11) NOT NULL,
  `fk_job_type` int(11) NOT NULL,
  `fk_place` int(11) DEFAULT NULL,
  `job_identifier` varchar(20) DEFAULT NULL,
  `external_job_id` varchar(50) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `short_description` text,
  `status` enum('new','active_expiration_reminded','active','inactive','expired') NOT NULL DEFAULT 'new',
  `expires_at` datetime DEFAULT NULL,
  `nb_vacancies` int(11) NOT NULL DEFAULT '1',
  `featured_rank` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_job`),
  UNIQUE KEY `job_U_1` (`job_identifier`),
  UNIQUE KEY `job_slug` (`slug`),
  KEY `job_FI_1` (`fk_company`),
  KEY `job_FI_2` (`fk_user`),
  KEY `job_FI_3` (`fk_job_category`),
  KEY `job_FI_4` (`fk_job_type`),
  KEY `job_FI_6` (`fk_place`),
  KEY `job_FI_5` (`fk_place`),
  CONSTRAINT `job_FK_1` FOREIGN KEY (`fk_company`) REFERENCES `company` (`id_company`),
  CONSTRAINT `job_FK_2` FOREIGN KEY (`fk_user`) REFERENCES `user` (`id_user`),
  CONSTRAINT `job_FK_3` FOREIGN KEY (`fk_job_category`) REFERENCES `job_category` (`id_job_category`),
  CONSTRAINT `job_FK_4` FOREIGN KEY (`fk_job_type`) REFERENCES `job_type` (`id_job_type`),
  CONSTRAINT `job_FK_5` FOREIGN KEY (`fk_place`) REFERENCES `place` (`id_place`)
) ENGINE=InnoDB AUTO_INCREMENT=27630 DEFAULT CHARSET=utf8 |

SHOW CREATE TABLE company;

    CREATE TABLE `company` (
      `id_company` int(11) NOT NULL AUTO_INCREMENT,
      `fk_place` int(11) DEFAULT NULL,
      `fk_industry` int(11) DEFAULT NULL,
      `fk_modified_by_user` int(11) DEFAULT NULL,
      `name` varchar(255) NOT NULL,
      `featured_rank` int(11) NOT NULL DEFAULT '0',
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      `deleted_at` datetime DEFAULT NULL,
      `slug` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id_company`),
      UNIQUE KEY `company_slug` (`slug`),
      KEY `company_I_1` (`name`),

SHOW CREATE TABLE job_application;

CREATE TABLE `job_application` (
  `id_job_application` int(11) NOT NULL AUTO_INCREMENT,
  `fk_user` int(11) NOT NULL,
  `fk_job` int(11) NOT NULL,
  `status` enum('new','application_sent','approval_pending','in_call','invalid','jobseeker_notified','improvement_pending','read_pending','application_read','read_pending_reminded','manual_interaction_pending') NOT NULL DEFAULT 'new',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id_job_application`),
  KEY `job_application_FI_1` (`fk_user`),
  KEY `job_application_FI_2` (`fk_job`),
  CONSTRAINT `job_application_FK_1` FOREIGN KEY (`fk_user`) REFERENCES `user` (`id_user`),
  CONSTRAINT `job_application_FK_2` FOREIGN KEY (`fk_job`) REFERENCES `job` (`id_job`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf

MySql Version:

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.22                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.22-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

Answer 1

Three points that the query can be improved:

• remove the join to company. The joining condition is through a non-nullable foreign key, so it should always be true.
• change the GROUP BY x ORDER BY x DESC to: GROUP BY x DESC. This will avoid the extra sorts. Note that the syntax is under deprecation, so you may need to change it back in a future mysql upgrade.
• add an index on (deleted_at, id_job). This is essential for this query. In general, an index on a flag (true/false) column or one that has few distinct values is often useless. But the deleted_at column is a timestamp so the index can be useful in other queries as well. This query has the extra ORDER BY / LIMIT, so the 2-column index can be used effectively.

The query rewritten:

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
  job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
  job.short_description, job.status, job.expires_at, job.nb_vacancies, 
  job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
  COUNT(fk_job) AS application_count 
FROM job
  LEFT JOIN job_application ON job.id_job = job_application.fk_job 
WHERE job.deleted_at IS NULL  
GROUP BY job.id_job DESC 
LIMIT 50 ;

You could also use this version, which forces using the index and avoids the GROUP BY in the main query (groups by in the inline subquery which will be run only for 50 values):

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, 
  job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title,
  job.short_description, job.status, job.expires_at, job.nb_vacancies, 
  job.featured_rank, job.created_at, job.updated_at, job.deleted_at, job.slug, 
  ( SELECT COUNT(*)
    FROM job_application AS ja
    WHERE j.id_job = ja.fk_job  
  ) AS application_count 
FROM 
  ( SELECT id_job
    FROM job
    WHERE deleted_at IS NULL  
    ORDER BY id_job DESC 
    LIMIT 50
  ) AS j
  JOIN job ON job.id_job = j.id_job  
ORDER BY j.id_job DESC  ;