Mysql – SELECT with large WHERE IN clause taking long time

MySQLperformanceselect

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?

Best Answer

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 JOIN:

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 docID, patID, should have an index.

Related Solutions

Mysql – Query taking long time

First, it would be good if you had written the query using the explicit JOIN syntax and adding the table names in columns:

SELECT pd.domain, fd.passkeyid
FROM  portfolio.domains AS pd
   JOIN
      fabulousdomains.domains AS fd
         ON  pd.domain = fd.domain
         AND pd.owner = fd.owner
WHERE pd.owner = '15940'
  AND pd.groupid = '28555'
  AND fd.status = 'a'
  AND fd.listingqa NOT IN ( 'default', 'ok' ) ;

This can help identify a good indexing pattern for this query. You can try adding these indexes:

portfolio.domains (groupid, owner, domain)

fabulousdomains.domains (status, owner, domain, listingqa, passkeyid)

MySQL – Optimal Table Design for Medical Center Rental Service and Recurrent Shifts

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).

Best Answer

Related Solutions

Mysql – Query taking long time

MySQL – Optimal Table Design for Medical Center Rental Service and Recurrent Shifts

Related Question