CODEWITHSUNDEEP
phpmysqlsql
Scott Thompson
Scott Thompson

Reputation: 11

LEFT JOIN taking ages in mySQL

I have a mySQL query with a LEFT JOIN that is taking over 30 seconds to run and I need to drastically speed it up. The query is as follows:

SELECT asset.asset_id, schedule.schedule_name, asset.asset_type, asset_sign.sign_type, 
       asset.creation_date, asset.update_date, asset.sms, asset.gps, survey.location, 
       survey.address, asset.condition_grade, 
       asset.performance_grade, asset.aesthetics_grade  
FROM survey, SCHEDULE, asset 
  LEFT JOIN asset_sign 
   ON asset_sign.asset_id = asset.asset_id 
WHERE asset.sms = survey.sms 
      AND asset.deletion_date='0000-00-00' 
      AND asset.schedule_id = schedule.schedule_id 
      AND schedule.schedule_id = '1'

The Asset and Asset Tables are pretty large with around 10,000 lines.

Any help is much appreciated.

Upvotes: 0

Views: 50

Answers (2)

AlVaz
AlVaz

Reputation: 766

  1. make sure that asset_sign.asset_id is indexed
  2. make sure that asset.asset_id is indexed

  3. Rewrite your query for clarity:

    SELECT <whatever> 
FROM survey  
    JOIN asset ON asset.sms = survey.sms  
    JOIN schedule ON asset.schedule_id = schedule.schedule_id  
    LEFT JOIN asset_sign ON asset_sign.asset_id = asset.asset_id  
WHERE asset.deletion_date='0000-00-00'  
    AND schedule.schedule_id = '1'

  4. make sure that asset.sms is indexed

  5. make sure that survey.sms is indexed
  6. make sure that asset.schedule_id is indexed
  7. make sure that schedule.schedule_id is indexed

This is at a minimum as you should be joining on things that are indexed. You can also:

  1. index the columns you are using in your WHERE clause

  2. move the conditionals in your WHERE clause into their respective JOIN conditions so that the filtering happens at the JOIN step instead of after everything has been joined.

    SELECT <whatever> 
FROM survey  
    JOIN asset ON asset.sms = survey.sms AND asset.deletion_date='0000-00-00'
    JOIN schedule ON asset.schedule_id = schedule.schedule_id AND schedule.schedule_id = '1'
    LEFT JOIN asset_sign ON asset_sign.asset_id = asset.asset_id

Upvotes: 3

Rahul
Rahul

Reputation: 77876

You should consider modifying your query to have the proper joins like below. Also, move the conditions from WHERE to JOIN ON clause to have a proper outer join effect.

SELECT asset.asset_id, 
       schedule.schedule_name, 
       asset.asset_type, 
       asset_sign.sign_type, 
       asset.creation_date, 
       asset.update_date, 
       asset.sms, 
       asset.gps, 
       survey.location, 
       survey.address, asset.condition_grade, 
       asset.performance_grade, asset.aesthetics_grade  
FROM survey 
LEFT JOIN assest ON asset.sms = survey.sms 
AND asset.deletion_date='0000-00-00'
LEFT JOIN asset_sign ON asset_sign.asset_id = asset.asset_id 
LEFT JOIN SCHEDULE ON asset.schedule_id = schedule.schedule_id  
AND schedule.schedule_id = '1';

Upvotes: 2

Related Questions