MySQL Table with 7000 records - Too Slow

Databases
1

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

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.

3 
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?

4

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!!!

5

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.

6

So what I did is make the DoctorID in DoctorClinic Table into an Index. Is that it?

7

Haha! I think that worked! It loads in a second!!! So simple!!! Why didn’t I know about that before! Thanks everyone!!!

8

Some helpful info I recently received directed towards optimizing a database and the structure.

database model - post 12

database model - post 14