OK, see notes below...
SELECT
P.Patient_ID
, P.Patient_Account_No
, Concat(P.First_Name,' ',P.Last_Name) as Patient_Name
, P.Primary_Care_Physician
, max_appt_dates.Date as Last_Appointment_Date
, A.Resource_ID as Provider_ID
, A.POS_ID as Site_ID
, S.Name as Site_Name
FROM patient_details P
# Join a derived table of patient's last appot dates...
LEFT JOIN (
SELECT patient_id, MAX(date) as max_appt_date
FROM appointment_details
WHERE Practice_ID = ?
AND Status_ID <> 0
GROUP BY patient_id
) as max_appt_dates
ON P.patient_id = max_appt_dates.patient_id
# Then inner join to real appt date
# record to get secondary info on appt...
INNER JOIN appointment_details A
ON P.patient_id = A.patient_id
AND A.date = max_appt_dates.max_appt_date
# Lookup service provider's site info...
LEFT JOIN place_of_service_details S
ON A.POS_ID = S.POS_ID
AND S.Practice_ID = ?
WHERE P.Practice_ID = ?
# Really unsure about the below WHERE condition, see notes...
AND A.Date = ?
ORDER BY ?
LIMIT ?, ?
Some basic changes to remove the correltated subquery from your original post with a more-efficient derived table, then joining the derived table back to the appointment table using the last appointment date and the patient's ID as the join criteria.
Some notes:
In your original query, you had the following WHERE condition:
AND A.Date=now()
Don't know if you realized it or not, but this WHERE condition will essentially limit the return to only those patient's which have a *LAST* appointment date on today's date. If that's what you are looking for, OK, but I wanted to ask.
Secondly, if you really do want to keep the above WHERE condition, you will want to remove change it to what I put in the code above:
AND A.Date = ?
Why? Well, using the NOW() function (which is a non-deterministic function) precludes the query from ever being put into the query cache (not a good thing). Better to have your application pass through a *date* value to the parameter. That way MySQL can employ the query cache so that returns of this query will be instantaneous after the first run of the day...
Thirdly, I remove all code referencing the provider_details table. It wasn't used anywhere in the query and so was just wasting CPU cycles and memory.
Fourthly, for optimal performance, please ensure indexes on the following table/fields:
appointment_details (patient_id, date)
appointment_details (Practice_ID) (however, see my note below on this...)
patient_details (Practice_ID)
place_of_service_details (Practice_ID)
Lastly, though I'm not familiar with your exact schema, the re-occurence of the following:
WHERE Practice_ID=?
is a little troubling and may indicate that the database isn't properly normalized. Could you explain hy the Practice_ID column is necessarily repeated in all of the tables (as opposed to a single table)?
Thanks, and HTH,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com