How to use mysql's EXPLAIN to look for possible problems

Question

Stress testing a site & everything is breaking, obviously.

Today's problem: WSOD on several pages. After a few hours I have narrowed the problem on one page down to this query (I hope): It used to run in a second; now it takes > 300.

SELECT   jobs.posting_date                          ,
         jobs.id                                    ,
         jobs.title                                 ,
         addresses.street                           ,
         cities.name                                ,
         states.abbr                                ,
         details.target_url                         ,
         details.description_extracted AS extraction,
         COUNT(jobs_skills.skill_id)   AS skills    ,
         users.first_name
FROM     jobs
         JOIN addresses
         ON       addresses.id = jobs.address_id
         JOIN states
         ON       addresses.state_id = states.id
         JOIN cities
         ON       addresses.city_id = cities.id
         JOIN job_feed_details AS details
         ON       jobs.id = details.job_id
         LEFT JOIN jobs_skills
         ON       jobs.id = jobs_skills.job_id
         LEFT JOIN users
         ON       users.id = details.user_id
WHERE    details.moderated = 0
AND      expiration        = 0
GROUP BY jobs.id
ORDER BY jobs.posting_date DESC

Running EXPLAIN I get this:

id  select_type table   type    possible keys           key  key_len    ref                     rows    extra
1   SIMPLE  details ALL job_id                                      537704                              Using where; Using temporary; Using filesort
1   SIMPLE  jobs        eq_ref  PRIMARY,address_id_indexPRIMARY 4   557574_dev.details.job_id       1   Using where
1   SIMPLE  addresses   eq_ref  PRIMARY             PRIMARY     4   557574_dev.jobs.address_id      1   Using where
1   SIMPLE  states      eq_ref  PRIMARY             PRIMARY     1   557574_dev.addresses.state_id   1   Using where
1   SIMPLE  cities      eq_ref  PRIMARY             PRIMARY     4   557574_dev.addresses.city_id    1   
1   SIMPLE  jobs_skills ref     Job_skill           Job_skill   4   557574_dev.jobs.id              4   Using index
1   SIMPLE  users       eq_ref  PRIMARY             PRIMARY     3   557574_dev.details.user_id      1   

looking at the EXPLAIN is it possible to tell

• If there are any full table scans happening
• If any relevant incises are missing
• Which table or join is being so slow
• Any other useful information in my 'quest to find the slow table'

---

Update: Running the query again without the group_by (and related table joins); still is requiring a temp table and filesort, so it seems it is an index issue. Will begin looking at all the tables for missing indices.

Answer 1

what indices do you have defined?

If you index jobs.address_id, addresses.state_id, addresses.city_id, details.job_id, jobs_skills.job_id, details.user_id, and jobs.posting_date you should be able to do the entire join from indices without hitting the underlying table and run the ordering with an index.

Also, are jobs inserted in posting_date order? If so, you can order by id instead of by posting_date, which will be faster since it's a primary key.

The explain plan looks like the majority of the processing is in the grouping and ordering. You've got a filesort and temporary table in the final step, which is pretty expensive. In addition, it looks like you're using where in places where you probably should be using index, so you might want to make sure all the association columns are indexed.

I'd recommend loading up the data in your sandbox and playing with index combinations until your explain plan uses more indices and hopefully no temporary tables or filesorting. You might have some difficulty with that last part though as grouping tends to be expensive.

Does that help?

Answer 2

Forgive me if I am wrong, but it seems like under the column 'extra' in the generated explain plan it is specified for you if an index will be used or not for the specified table and used key , e.g. table: addresses and key jobs.address_id, no index is used.

So all you need to do is note the columns of where you see 'where' under extra column. For such a table you can consider making an index.

Adding an index on the largest table will obviously have the greatest effect on performance, and I believe you should start there.