SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Philippines
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL Table with 7000 records - Too Slow

    Hi. I'm a bit of a beginner when it comes to SQL statements, so here goes.

    I have a table of doctors with about 7000 records, I JOIN it with another table, DoctorClinics, with as many records. It takes about 7 seconds for it to load. Here is my statement:

    SELECT Doctor.DoctorID, Title1, Name, Title2, SpecialtyID, DoctorClinic.DoctorClinicID, SubscriptionExpiry
    FROM Doctor
    LEFT JOIN DoctorClinic ON Doctor.DoctorID=DoctorClinic.D?octorID
    GROUP BY DoctorID
    ORDER BY Subscribed DESC, Name

    What it does is it fetches the doctor's info from the Doctor Table and the ClinicID from the DoctorClinic Table, each Doctor may have multiple clinic entries.

    Is there anyway I can optimize this or make it faster? It seems to take a long time to load, more than 5 seconds, but when I remove the LEFT JOIN clause, it takes less than a second.

    Although, sometimes, I need to search Clinic locations, which is stored in the DoctorClinic table. I add this where clause:

    WHERE DoctorClinic.CityID = '3'

    Any help would be much appreciated.

    Cheers!

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,016
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SHOW INDEXES FROM Doctor
     
    SHOW INDEXES FROM DoctorClinic

    Can you post the output from them which will show what indexes are in place?

    In the LEFT JOIN you have:

    LEFT JOIN DoctorClinic ON Doctor.DoctorID=DoctorClinic.D?octorID
    Is that ? in the actual query or is it a typo when typing your post?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Philippines
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi. Yup, that's a typo on the post. Sorry about that.

    The only indexes I have are PRIMARY, DoctorID for the Doctor table and DoctorClinicID for the DoctorClinic table.

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,812
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    So adding an index to the DoctorClinic table to provide direct access to DoctorID instead of having to read the entire table should be the first thing to do to speed things up.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Philippines
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright! Since I'm a newbie at MySQL databases, how do I do this? Can you point me in the right direction, like a tut, or something to read about indexes?

    Thanks!!!!!

  6. #6
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Philippines
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what I did is make the DoctorID in DoctorClinic Table into an Index. Is that it?

  7. #7
    SitePoint Member
    Join Date
    Jul 2011
    Location
    Philippines
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha! I think that worked! It loads in a second!!!! So simple!!! Why didn't I know about that before! Thanks everyone!!!

  8. #8
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some helpful info I recently received directed towards optimizing a database and the structure.

    database model - post 12

    database model - post 14


Tags for this Thread

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
  •