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 |
+-------------------------+------------------------------+
Best Answer
Three points that the query can be improved:
company
. The joining condition is through a non-nullable foreign key, so it should always be true.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.(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 thedeleted_at
column is a timestamp so the index can be useful in other queries as well. This query has the extraORDER BY / LIMIT
, so the 2-column index can be used effectively.The query rewritten:
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):