Join 3 MySQL Tables Together

Pawel_Kowalski
Pawel_Kowalski used Ask the Experts™
on
Hello,

I have a very large database I am dealing with and am having trouble getting results in a single query for 3 database tables.

I attached a code snippet of these 3 tables with sample data.

What I need to do is this:

1. Loop through zSS_ndc_medid
2. JOIN zSS_ndc_medid to RM_MEDID using NDC
3. For each row in zSS_ndc_medid take the NDC from that row and search through zSS_formulary_list for this NDC. However, results must be limited to any row that has a value of provider=RXHUBPBM and List_ID=FSL101 (results wont always be found as there wont always be the NDC from this row in the zSS_formulary_list table).

Currently I have no problem with number 1 or 2. I can properly JOIN the two tables based on NDC and display results. But when I want to search through zSS_formulary_list I have to create another query inside of a loop which is really slow. Is there a way to combine all this in to a single query?

If it's impossible to do this in a single query are there any other optimization tips I can get? I can not change the format of this data, but I can add additional tables.

Help would be much appreciated. Thank you!

zSS_ndc_medid

NDC	|MEDID	|GNI	
-------------------------
1001	|11	|2
1002	|11	|2
1003	|11	|3
1004	|11	|2
1005	|13	|2
1006	|16	|2


zSS_formulary_list

NDC		|List_ID	|Provider	|Status		
-----------------------------------------------------------------
1001		|FSL101		|RXHUBPBM	|2
1002		|FSL101		|RXHUBPBM	|2
1003		|FSL101		|RXHUBPBM	|5
1004		|FSL101		|RXHUBPBM	|2
1001		|FSL102		|RXHUBPBM	|2
1002		|FSL102		|RXHUBPBM	|2
1003		|FSL103		|RXHUBPBM	|5
1004		|FSL104		|RXHUBPBM	|2

RM_MEDID

MED_ID		|Name		|
---------------------------------
11		|One		|
13		|Two		|
16		|Three		|

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
This should be sufficient:
SELECT {column list}
FROM zSS_ndc_media a
JOIN RM_MEDID b ON a.MEDID = b.MED_ID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
   AND c.List_ID = 'FSL101' AND c.Provider = 'RXHUBPBM'
;

Open in new window

Author

Commented:
Thank you for the fast reply, the query seems to be working. However, since I am dealing with around 130,000 records it's really slow.  To get 10 records it takes about 20 seconds. That way I am looking at about 6 hours of processing. Is there any possible way to speed this up? This is something that is done through console and doesn't need to be instant, but 6 hours is a bit much.

Since the table names and fields are slightly different in the production databse my actual query is as follows:

SELECT 
    a.NDC, a.MEDID, a.BN, a.GNI, b.MED_MEDID_DESC, b.MEDID, c.NDC, c.provider, c.list_id, c.formulary_status
FROM zSS_ndc_medid a
JOIN RMIID1_MED b ON a.MEDID = b.MEDID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
   AND c.list_id = 'FSL101' AND c.provider = 'RXHUBPBM' 

Open in new window


Thanks again for the help.
Sounds like you need to setup a couple indexes to speed things up.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Indexes are likely the issue.
Please post results of:
EXPLAIN SELECT 
    a.NDC, a.MEDID, a.BN, a.GNI, b.MED_MEDID_DESC, b.MEDID, c.NDC, c.provider, c.list_id, c.formulary_status
FROM zSS_ndc_medid a
JOIN RMIID1_MED b ON a.MEDID = b.MEDID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
   AND c.list_id = 'FSL101' AND c.provider = 'RXHUBPBM'

Open in new window


See this Article for explanation of EXPLAIN plus some other great tips on MySQL query speed.
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

Author

Commented:
Thank you so much for the help, indexing fixed the speed issue and everything else seems to be working well. I am not getting some NDC results for some of the rows as expected but I think I can work this out, if not I'll start another questions. Thansk again.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Try this, then if it doesn't work, we'll be around to help when you open your related question.
SELECT {column list}
FROM zSS_ndc_media a
LEFT JOIN RM_MEDID b ON a.MEDID = b.MED_ID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
   AND c.List_ID = 'FSL101' AND c.Provider = 'RXHUBPBM'
;

Open in new window


Glad we could help on this one.

Best regards and happy coding,
Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial