Slow running MySQL query - how to optimize?

Question

I'm running a query, and struggling to understand why MySQL is runing it so slowly. I've included the actual queries and the EXPLAIN in 3 scenarios below.

The first query is very slow to run (23 seconds), but removing just one field (Query 2) from the where clause causes the query to run in 0.010s. This is a TinyInt field (essentially a boolean 0/1 is stored).

projects -> hasMany -> milestones -> hasMany -> tasks

task_wishlist is a pivot table between tasks and wishlists

QUERY 1

SELECT `tasks`.*,
        task_wishlist.description AS item_description,
        task_wishlist.created_at AS item_created_at
FROM `tasks`
LEFT JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id`
LEFT JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id`
LEFT JOIN `projects` ON `milestones`.`project_id` = `projects`.`id`
WHERE `task_wishlist`.`wishlist_id` = '527021'
  AND `tasks`.`active` = '1'
  AND `projects`.`active` = '1'
  AND `milestones`.`active` = '1'
ORDER BY `task_wishlist`.`created_at` DESC
LIMIT 25;
/* Affected rows: 0  Found rows: 25  Warnings: 0  Duration for 1 query: 23.072 sec. (+ 0.040 sec. network) */

Query 1 EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: projects
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 997
     filtered: 10.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: milestones
   partitions: NULL
         type: ref
possible_keys: PRIMARY,milestones_project_id_foreign
          key: milestones_project_id_foreign
      key_len: 4
          ref: fusion.projects.id
         rows: 3
     filtered: 10.00
        Extra: Using index condition; Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: tasks
   partitions: NULL
         type: ref
possible_keys: PRIMARY,tasks_milestone_id_foreign
          key: tasks_milestone_id_foreign
      key_len: 5
          ref: fusion.milestones.id
         rows: 5
     filtered: 10.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: task_wishlist
   partitions: NULL
         type: ref
possible_keys: task_wishlist_wishlist_id_foreign,task_wishlist_task_id_foreign
          key: task_wishlist_task_id_foreign
      key_len: 4
          ref: fusion.tasks.id
         rows: 100
     filtered: 0.28
        Extra: Using where
4 rows in set, 1 warning (0.01 sec)

Query 2 (remove milestones.active)

SELECT `tasks`.*,
       task_wishlist.description AS item_description,
       task_wishlist.created_at AS item_created_at
FROM `tasks`
LEFT JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id`
LEFT JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id`
LEFT JOIN `projects` ON `milestones`.`project_id` = `projects`.`id`
WHERE `task_wishlist`.`wishlist_id` = '527021'
  AND `tasks`.`active` = '1'
  AND `projects`.`active` = '1'
  /*AND `milestones`.`active` = '1'*/
ORDER BY `task_wishlist`.`created_at` DESC
LIMIT 25;
/* Affected rows: 0  Found rows: 25  Warnings: 0  Duration for 1 query: 0.028 sec. (+ 0.010 sec. network) */

Query 2 Explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: task_wishlist
   partitions: NULL
         type: ref
possible_keys: task_wishlist_wishlist_id_foreign,task_wishlist_task_id_foreign
          key: task_wishlist_wishlist_id_foreign
      key_len: 4
          ref: const
         rows: 7224
     filtered: 100.00
        Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tasks
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,tasks_milestone_id_foreign
          key: PRIMARY
      key_len: 4
          ref: fusion.task_wishlist.task_id
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: milestones
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,milestones_project_id_foreign
          key: PRIMARY
      key_len: 4
          ref: fusion.tasks.milestone_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: projects
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: fusion.milestones.project_id
         rows: 1
     filtered: 10.00
        Extra: Using where
4 rows in set, 1 warning (0.00 sec)

Query 3: remove projects.active = 1

SELECT `tasks`.*,
       task_wishlist.description AS item_description,
       task_wishlist.created_at AS item_created_at
FROM `tasks`
LEFT JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id`
LEFT JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id`
LEFT JOIN `projects` ON `milestones`.`project_id` = `projects`.`id`
WHERE `task_wishlist`.`wishlist_id` = '527021'
  AND `tasks`.`active` = '1'
  /*AND `projects`.`active` = '1'*/
  AND `milestones`.`active` = '1'
ORDER BY `task_wishlist`.`created_at` DESC
LIMIT 25;
/* Affected rows: 0  Found rows: 25  Warnings: 0  Duration for 1 query: 0.027 sec. (+ 4.031 sec. network) */

Query 3 Explain

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: task_wishlist
   partitions: NULL
         type: ref
possible_keys: task_wishlist_wishlist_id_foreign,task_wishlist_task_id_foreign
          key: task_wishlist_wishlist_id_foreign
      key_len: 4
          ref: const
         rows: 7224
     filtered: 100.00
        Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tasks
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,tasks_milestone_id_foreign
          key: PRIMARY
      key_len: 4
          ref: fusion.task_wishlist.task_id
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: milestones
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: fusion.tasks.milestone_id
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: projects
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: fusion.milestones.project_id
         rows: 1
     filtered: 100.00
        Extra: Using index
4 rows in set, 1 warning (0.00 sec)

What can I do to make this query run properly with all active fields included?

Answer 1

You say task_wishlist is a many-to-many mapping table between tasks and wishlists? I'll bet it does not have decent indexes. Follow the tips here:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

If that does not suffice, then come back with SHOW CREATE TABLE for each of the tables.

Answer 2

One fact you need to relize is that your query in fact is doing simple INNER JOINs but not LEFT JOINS.
The Left outer join basically works in the same way as inner join exept that it returns NULLs if the join-condition does not find any matching row in the "right" table.
But all NULLs in your query are filtered out by conditions in the WHERE clause, please examine closely the query:

FROM `tasks`
LEFT JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id`
LEFT JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id`
LEFT JOIN `projects` ON `milestones`.`project_id` = `projects`.`id`
WHERE `task_wishlist`.`wishlist_id` = '527021'  
  AND `tasks`.`active` = '1'  
  AND `milestones`.`active` = '1'
  AND `projects`.`active` = '1' 

• nulls from LEFT JOIN task_wishlist are filtered out by WHERE task_wishlist.wishlist_id = '527021'
• nulls from LEFT JOIN milestones are filtered out by WHERE milestones.active = '1'
• nulls from LEFT JOIN projects are filtered out by WHERE projects.active = '1'

MySql is aware of that and it internally rewrites your query into INNER JOIN query:

FROM `tasks`
INNER JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id`
INNER JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id`
INNER JOIN `projects` ON `milestones`.`project_id` = `projects`.`id`
WHERE `task_wishlist`.`wishlist_id` = '527021'  
  AND `tasks`.`active` = '1'  
  AND `milestones`.`active` = '1'
  AND `projects`.`active` = '1' 

---

If you remove (comment) some condition from the WHERE clause, this "activates" corresponding LEFT JOIN part of the query, so semantics of the query changes and it's resultset also changes. It does not make any sense to compare a performance of such queries, because they are frankly different, and in fact you are comparing apples to oranges.

---

Since the query is doing simple INNER JOINs, you can rewrite it to this one:

FROM `tasks`
INNER JOIN `task_wishlist` ON `tasks`.`id` = `task_wishlist`.`task_id` 
                                         AND `task_wishlist`.`wishlist_id` = '527021' 

INNER JOIN `milestones` ON `tasks`.`milestone_id` = `milestones`.`id` 
                                                AND `milestones`.`active` = '1'

INNER JOIN `projects` ON `milestones`.`project_id` = `projects`.`id` 
                                                 AND `projects`.`active` = '1' 

and now in the conditions above you can easily see "pairs" of columns which are used during a join:

• projects.id + projects.active
• milestones.id + milestones.active
• task_wishlist.task_id + task_wishlist.wishlist_id

Try to add a multicolum indexes on there "pairs", one example for projects table:

CREATE INDEX xxx ON projects( id, active )

If id column is also a primary key, then it may be enoung to add indexes only on single active column instead of multicolumn (id, active) since in InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.
You must experiment yourself.