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?