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