MySQL Join Sub-Select Optimization

Question

UPDATE users u
JOIN (select count(*) as job_count, user_id from job_responses where date_created > subdate(now(), 30) group by user_id) j
ON j.user_id = u.user_id
JOIN users_profile p
ON p.user_id = u.user_id
JOIN users_roles_xref x
ON x.user_id = u.user_id
SET num_job_responses = least(j.job_count, 5)
WHERE u.status = 1 AND p.visible = "Y" AND x.role_id = 2000

And explain tells me this:

+----+-------------+---------------+--------+---------------------------------+---------------+---------+----------------------+--------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys                   | key           | key_len | ref                  | rows   | Extra                                        |
+----+-------------+---------------+--------+---------------------------------+---------------+---------+----------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>    | ALL    | NULL                            | NULL          | NULL    | NULL                 |  23008 |                                              | 
|  1 | PRIMARY     | u             | eq_ref | PRIMARY,user_id,status,status_2 | PRIMARY       | 4       | j.user_id            |      1 | Using where                                  | 
|  1 | PRIMARY     | p             | ref    | user_id,visible                 | user_id       | 4       | scoop_jazz.u.user_id |      2 | Using where                                  | 
|  1 | PRIMARY     | x             | ref    | index_role_id,index_user_id     | index_user_id | 4       | scoop_jazz.u.user_id |      3 | Using where                                  | 
|  2 | DERIVED     | job_responses | range  | date_created                    | date_created  | 4       | NULL                 | 135417 | Using where; Using temporary; Using filesort | 
+----+-------------+---------------+--------+---------------------------------+---------------+---------+----------------------+--------+----------------------------------------------+

I'm having trouble optimizing this query with explain. Any way to do it?

RichardTheKiwi · Accepted Answer · 2011-02-08 20:10:20Z

1

You will want to add an index on job_responses(date_created, user_id). Then you can drop the current single-column index on date_created.

The most expensive part of the query is the subquery

(select count(*) as job_count, user_id
 from job_responses
 where date_created > subdate(now(), 30)
 group by user_id)

The only two fields of note are user_id and date_created. There is an index on date_created that has been chosen to satisfy date_created in last 30 days. However, it will have to go back to the data pages to retrieve user_id, then group by it.

If you had a composite index, the user_id is available directly from the index. It also covers the single-column index date_created, so you can drop that one.

edited Feb 8, 2011 at 20:10

answered Feb 8, 2011 at 19:57

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zombor Over a year ago

It did indeed choose that index, however explain and query time remain basically unchanged.

zombor · Accepted Answer · 2011-03-02 17:18:48Z

0

It ended up being easier and way faster to generate a temporary table, populate it, and then use a join on that table. I was "chunking" the original query, which ends up being very expensive when it has to create and destroy tables created by sub-selects.

answered Mar 2, 2011 at 17:18

zombor

3,25720 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL Join Sub-Select Optimization

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