I’m pretty new at all this so be gentle :).
I have large medical DB where I need to extract data for patients based on selected doctor(s).
Right now I build a patient list by
SELECT patID FROM appointments WHERE docID IN ('docid1', 'docid2', ..... , 'docidn')
and once i get my patient list I use it when extracting data from a whole bunch of patient related tables.
Depending on my initial doctors list I can end up with patient lists that are 20,000+. The total # of patients in the system is close to 1,000,000.
I then take my patient list and start getting data from other tables, more or less like this
Select * FROM xrays WHERE patID IN ('patid1', 'patid2', 'paid3',....., 'patidn' )
where I build the list of patids based on my the list I have from the first part.
As you can imagine, with a patient list of 20,000, the above query is taking a long time.
Any suggestions on a better way to do this?
This is what Joins are for. You don’t have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with
SELECT x.* FROM xrays AS x JOIN appointments AS a ON a.patID = x.patID WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn') ORDER BY x.patID --- optional so your application gets --- the data of patients, ordered.
Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your
patID, should have an index.