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?

Did you try to create any non-unique index for each of these fields to see if that helps at all? — Efthymios Kalyviotis
– Efthymios Kalyviotis, Commented Sep 22, 2017 at 13:53
This format of explain is difficult to read, but the amount of rows that MySQL deals with is low. This implies there could be an I/O problem. Are you running a mechanical hard drive and default MySQL configuration? There's an additional profiling mechanism available in MySQL, called profiling. SET PROFILING = 1; Your query goes here ; SHOW PROFILE FOR QUERY 1; SET PROFILING = 0;. This will show you all steps that MySQL undertakes behind the scenes. Guessing blindly: your innodb_buffer_pool_size is too low, so MySQL needs to wait for HDD to complete its I/O, which is slow. — Mjh
– Mjh, Commented Sep 22, 2017 at 13:53
My understanding is that indexes on boolean-type fields are not of any benefit. There is no I/O problem - this is a mysql optimization issue - notice the using temporary in the first explain, but not the others. — JonoB
– JonoB, Commented Sep 22, 2017 at 14:07
@JonoB - Yes, indexing a boolean is usually useless. However a 'composite' index starting with a boolean may be beneficial. (I don't see an example of such in this Question.) — Rick James
– Rick James, Commented Sep 23, 2017 at 3:19

krokodilko · Accepted Answer · 2017-09-22 18:42:15Z

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.

Philosophically, ON should only say how the tables relate to each other. WHERE should contain any filtering. For INNER JOIN, the Optimizer does not care. For LEFT JOIN there is a semantic difference.

Rick James · Accepted Answer · 2017-09-23 03:15:25Z

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.

answered Sep 23, 2017 at 3:15

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Slow running MySQL query - how to optimize?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related