Doctrine / MySQL Slow query even when using indexes

First of all, latest test, with this query on MySQL Workbench returning 15000 rows, 0.407 sec / 0.640 sec thats the duration, but on another table with +50000 rows i have no problem with the duration 0.000 sec / 0.078 sec. The query is this: SELECT title FROM job; So the problem is with the table structure i believe.

Summarize the problem:

I've been days trying to figure out why my query is slow, with +10000 records. I'm using Symfony 4.3 and is a doctrine query, even testing this query in MySQL Workbench takes too much time. I think i builded the correct indexes and can't figure out whats the problem.

What have i try: I tryed to change the indexes, querying with DQL and non DQL, dividing the query, removing joins, simplifying the query, dividing the tables and much more.

Show some code: Here are some images of the main tables giving me the problem (first it was all in the same table Job, but i divided to see if the error was that):

I created a small SQL Fiddle if you need more data, i can try and add it: SQL Fiddle The create tables:

Job table CREATE statement

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `company_id` int(11) NOT NULL,
  `activity_sector_id` int(11) DEFAULT NULL,
  `status` int(11) NOT NULL,
  `active` datetime NOT NULL,
  `contract_type_id` int(11) NOT NULL,
  `salary_type_id` int(11) NOT NULL,
  `workday_id` int(11) NOT NULL,
  `geoname_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_FBD8E0F8979B1AD6` (`company_id`),
  KEY `IDX_FBD8E0F8398DEFD0` (`activity_sector_id`),
  KEY `IDX_FBD8E0F8CD1DF15B` (`contract_type_id`),
  KEY `IDX_FBD8E0F85248165F` (`salary_type_id`),
  KEY `IDX_FBD8E0F8AB01D695` (`workday_id`),
  KEY `IDX_FBD8E0F823F5422B` (`geoname_id`),
  KEY `created_at` (`created_at`),
  KEY `status` (`status`),
  KEY `active` (`active`),
  CONSTRAINT `FK_FBD8E0F823F5422B` FOREIGN KEY (`geoname_id`) REFERENCES `geo__name` (`id`),
  CONSTRAINT `FK_FBD8E0F8398DEFD0` FOREIGN KEY (`activity_sector_id`) REFERENCES `activity_sector` (`id`),
  CONSTRAINT `FK_FBD8E0F85248165F` FOREIGN KEY (`salary_type_id`) REFERENCES `job_salary_type` (`id`),
  CONSTRAINT `FK_FBD8E0F8979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_FBD8E0F8AB01D695` FOREIGN KEY (`workday_id`) REFERENCES `workday` (`id`),
  CONSTRAINT `FK_FBD8E0F8CD1DF15B` FOREIGN KEY (`contract_type_id`) REFERENCES `job_contract_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The JobContent table CREATE statement:

CREATE TABLE `job_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `minimum_experience` int(11) DEFAULT NULL,
  `min_salary` decimal(7,2) DEFAULT NULL,
  `max_salary` decimal(7,2) DEFAULT NULL,
  `description` varchar(3000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zip_code` int(11) DEFAULT NULL,
  `vacancies` int(11) DEFAULT NULL,
  `show_salary` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_642A8732BE04EA9` (`job_id`),
  KEY `title` (`title`(191)),
  CONSTRAINT `FK_642A8732BE04EA9` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

JobSalaryType CREATE statement:

CREATE TABLE `job_salary_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Job table:

JobContent table:

The doctrine query is the following:

return $this->createQueryBuilder('j')
            ->orderBy('j.createdAt', 'DESC')
            ->innerJoin('j.jobContent', 'jc')
            ->innerJoin('j.company', 'c')
            ->innerJoin('j.activitySector', 'act')
            ->innerJoin('j.SalaryType', 'st')
            ->innerJoin('j.ContractType', 'ct')
            ->innerJoin('j.geoname', 'gn')
            ->innerJoin('j.workday', 'wd')
            ->select([
                'j.id AS job_id',
                'j.status AS job_status',
                'j.active AS job_active',
                'j.createdAt AS job_createdAt',
                'c.name AS company_name',
                'c.logo AS company_logo',
                'act.name AS activitySector_name',
                'st.name AS salaryType_name',
                'ct.name AS contractType_name',
                'gn.name AS geoname_name',
                'wd.name AS workday_name',
                'jc.title AS job_title',
                'jc.showSalary AS job_showSalary',
                'jc.minSalary AS job_minSalary',
                'jc.maxSalary AS job_maxSalary',
            ])
            ->andWhere('j.status = 1')
            ->andWhere('j.active >= CURRENT_TIMESTAMP()')
            ->getQuery()->getArrayResult();

This query, has many joins as you can see, but thats not the problem because if i remove them the query still run slow: (query extract from symfony profiler)

SELECT 
  j0_.id AS id_0, 
  j0_.status AS status_1, 
  j0_.active AS active_2, 
  j0_.created_at AS created_at_3, 
  c1_.name AS name_4, 
  c1_.logo AS logo_5, 
  a2_.name AS name_6, 
  j3_.name AS name_7, 
  j4_.name AS name_8, 
  g5_.name AS name_9, 
  w6_.name AS name_10, 
  j7_.title AS title_11, => The query gets slow with this
  j7_.show_salary AS show_salary_12, => The query gets slow with this
  j7_.min_salary AS min_salary_13, => The query gets slow with this
  j7_.max_salary AS max_salary_14 => The query gets slow with this
FROM 
  job j0_ 
  INNER JOIN job_content j7_ ON j0_.id = j7_.job_id 
  INNER JOIN company c1_ ON j0_.company_id = c1_.id 
  INNER JOIN activity_sector a2_ ON j0_.activity_sector_id = a2_.id 
  INNER JOIN job_salary_type j3_ ON j0_.salary_type_id = j3_.id 
  INNER JOIN job_contract_type j4_ ON j0_.contract_type_id = j4_.id 
  INNER JOIN geo__name g5_ ON j0_.geoname_id = g5_.id 
  INNER JOIN workday w6_ ON j0_.workday_id = w6_.id 
WHERE 
  j0_.status = 1 
  AND j0_.active >= CURRENT_TIMESTAMP 
ORDER BY 
  j0_.created_at DESC

The explain query:

The query gets slow when i select something from the job table:

j7_.title AS title_11, 
j7_.show_salary AS show_salary_12, 
j7_.min_salary AS min_salary_13, 
j7_.max_salary AS max_salary_14 

I added an index to "title" field from the Job table.

If you need any more info i will give it to you. I really apreciate the time and help, thank you.

from Doctrine / MySQL Slow query even when using indexes