Mysql – Need to Rewrite/optimize Slow Query

MySQLmysql-5.5

I have a query with following explain plan

EXPLAIN SELECT COUNT(applicant.id) as applicantCount,
    application.name,applicant.application_id,application.id 
FROM  applicant 
  LEFT JOIN application 
    ON applicant.application_id = application.id 
  LEFT JOIN calendar 
    ON applicant.application_calendar_id = calendar.id 
  LEFT JOIN application_category 
    ON application_category.id = calendar.category_id 
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'  
GROUP BY application.name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: application_category
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: calendar
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: applicant
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2333634
        Extra: Using where; Using join buffer
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: application
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: eRecuitment_TestQueries.applicant.application_id
         rows: 1
        Extra: 
4 rows in set (0.00 sec)

Query takes 30 seconds to complete where tables application, application_category and calendar are master tables which have 3 to 4 records only. The applicant table has about 2400000 records.

Is there any other approach to rewrite/optimize this query?

Index details

  • Table application

    PRIMARY KEY (`id`)

  • Table applicant

    PRIMARY KEY (`id`),
KEY `idx_abdul_test` (`application_id`)

  • Table Calendar

    PRIMARY KEY (`id`)

  • Table application_category

    PRIMARY KEY (`id`)

Best Answer

The last two LEFT joins can be rewritten as INNER joins (due to the WHERE condition on the application_category table.

If application (name) is indeed unique, there is no difference if you group by that column or by application (id) or even by applicant (application_id) - the last one provided there is a FOREIGN KEY constraint from applicant towards application.

So, you could add an index on applicant (application_calendar_id, application_id)

and rewrite the query as:

SELECT 
    COUNT(*) AS applicantCount,
    application.name, 
    applicant.application_id,
    application.id 
FROM  applicant 
  LEFT JOIN application 
    ON applicant.application_id = application.id 
  JOIN calendar 
    ON applicant.application_calendar_id = calendar.id 
  JOIN application_category 
    ON application_category.id = calendar.category_id 
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'  
GROUP BY applicant.application_id ;
Related Question