Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: What's Wrong With This Query?

    Anyone know why this query is so slow? It the nature of a left join just slow? Any suggestions on how to write it better? Thanks in advance for the help.

    NOTES: All tables have a unique, auto-incremented, primary key.


    SELECT res_id, resumes.location_id, locations.location_id, locations.location_name, resumes.priority, users.first_name, users.last_name, DATE_FORMAT(`posted_date`, '%m.%d.%y') AS posted_date, resumes.job_title, resumes.experience
    FROM resumes, users
    LEFT JOIN locations ON resumes.location_id = locations.location_id
    WHERE locations.location_name LIKE 'US' and resumes.keywords LIKE '%assistant%' and users.username = resumes.username
    ORDER BY resumes.priority, posted_date DESC
    LIMIT $from, $max_results

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select res_id
         , resumes.location_id
         , locations.location_id
         , locations.location_name
         , resumes.priority
         , users.first_name
         , users.last_name
         , DATE_FORMAT(posted_date
                     , '%m.%d.%y') AS posted_date
         , resumes.job_title
         , resumes.experience 
      from resumes
    inner
      join users
        on resumes.username
         = users.username 
    left outer
      join locations 
        on resumes.location_id 
         = locations.location_id
       and locations.location_name = 'US' 
     where resumes.keywords LIKE '%assistant%' 
    order 
        by resumes.priority
         , posted_date DESC
    limit $from
         , $max_results
    make sure you have indexes on resumes.username, users.username, resumes.location_id, locations.location_id, and locations.location_name

    an index on resumes.keyword may not help because of the way you're searching it

    but at least the joins will be efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •