SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query taking forever to load

    The following query works, however it is taking forever to load the results. Can anyone offer some advice?

    Code MySQL:
    SELECT
    dba_case_tenant.case_no,
    dba_case_tenant.GranteeID,
    dba_case_tenant.UnitNum,
    dba_case_tenant.tenant_home_phone,
    dba_case_tenant.occ_date,
    dba_case_tenant.tenant_f_name,
    dba_case_tenant.tenant_l_name,
    dba_case_tenant.hohYN,
    dba_case_info.st_no,
    dba_case_info.st_name,
    dba_case_info.addressdescription,
    dba_case_info.ComplianceCertificate,
    funding.ID,
    funding.grant_no,
    units.UnitName
    FROM
    dba_case_tenant
    Inner Join dba_case_info ON dba_case_info.case_no = dba_case_tenant.case_no
    Inner Join units ON units.Case_No = dba_case_tenant.case_no AND units.UnitNumber = dba_case_tenant.UnitNum
    Inner Join funding ON funding.ID = dba_case_info.grant_ID
    WHERE
    dba_case_tenant.GranteeID =  '12' AND
    dba_case_tenant.hohYN =  'yes' AND
    grant_no <> 'Lead Free' AND 
    dba_case_info.ComplianceCertificate IS NOT NULL
    order by dba_case_info.st_name, dba_case_info.st_no

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Well, a couple things I noticed....
    1. Are your IDs numeric or strings? If they contain numeric values, they should be numeric.
    2. Do you have indexes on all of the appropriate values? While #1 can potentially slow you down, not having indexes will kill you. At the very least, you should have indexes on all the fields being joined, and then also on the fields being searched upon.
    3. If you have a finite number of values for grant_no, then searching for those specifically will speed up the search as it won't have to do a record by record scan and it can just do specific index searches (again, if indexes are defined)
    That's what I see, but I'm sure Rudy'll be in here soon laying the smackdown on me if I gave you some boneheaded advice...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The indexes made all the difference. Thank you!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,253
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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