Dime Dime - 3 months ago 12
MySQL Question

How to select from first table if ID exist in second or third table with high efficiency?

I have 3 tables in assignment.
First table:
PATIENT: patid, fname, lname, recordnum.
PATIENTDR: patid, drid,
PATIENTCLIN: patid, clinid.

I need to select patients by fname, or lname if "PATID" in table "PATIENT" is found in table "PATIENTDR" if DRID="$docotr->id" OR "PATIENTCLIN" if CLINID="$clinic->clinid"

This is my query

SELECT t1.* FROM patient t1
WHERE patlname like '{$search_patlname}%'
AND patfname like '{$search_patfname}%'
AND
(
EXISTS
(SELECT patid FROM patientdr t2 WHERE t2.drid = '{$doctor->id}' AND t2.patid = t1.patid)

OR EXISTS

(SELECT patid FROM patientclin t3 WHERE t3.clinid = '{$clinic->clinid}' AND t3.patid = t1.patid)
)
LIMIT 10


The number of patients can be very high, data will be retrieved using ajax and taking in consideration following note:

"SQL statements that use the EXISTS Condition in MySQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition."

Is there a more efficient way to write this query, maybe i should break rules and insert patient fname and lname in tables patientdr and patientclin to reduce response time, or by limiting results to 10 rows is efficient enough ?
Thanks for all kind of help in advance.

Answer

As mentioned in the comments, this can be solved with a simple INNER JOIN:

SELECT t1.* 
FROM patient t1
INNER JOIN (
    SELECT patid 
    FROM patientdr t2 
    WHERE t2.drid = '{$doctor->id}' 
    UNION DISTINCT
    SELECT patid 
    FROM patientclin t3 
    WHERE t3.clinid = '{$clinic->clinid}'
) i
ON t1.patid = i.patid
WHERE patlname like  '{$search_patlname}%' 
AND patfname like '{$search_patfname}%' 
LIMIT 10

If you haven't read the documentation on INNER JOIN, it is worth taking the time to do so, and to play around with some queries that use it.

Good luck!

Comments