Solved

MySQL - Sub-queries killing query speed...looking to find a work around

Posted on 2013-02-01
9
251 Views
Last Modified: 2013-02-01
Hi,

Have the following query and the sub-queries are killing the speed and just wonder if there is any way to speed this up.

SELECT
table_a.item_number,
table_b.serial_number,
table_c.system_number,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL1') AS RPL1,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL2') AS RPL2,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL3') AS RPL3,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL4') AS RPL4,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL5') AS RPL5,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL6') AS RPL6,
(SELECT table_d.rpl_number FROM table_d WHERE (rpl_product = table_a.item_number)AND table_d.fco_number = 'RPL7') AS RPL7
FROM
table_a
LEFT JOIN table_b ON table_b.serial_number = table_a.item_number
LEFT JOIN table_c ON table_c.system_number = table_a.item_number
LEFT JOIN table_e ON table_e.user_name = table_a.user_name
LEFT JOIN table_f ON table_f.item_number = table_a.item_number
LEFT JOIN table_g ON table_g.id = table_a.fw_id
LEFT JOIN table_h ON table_h.id = table_a.hw_id
WHERE table_a.item_number IS NOT NULL

Open in new window


Shouldn't I be able to do an inner join instead of sub queries...but still retain the full result set from table_a?

Thanks!
0
Comment
Question by:mrh14852
  • 5
  • 3
9 Comments
 
LVL 12

Accepted Solution

by:
Jared_S earned 500 total points
ID: 38844583
It looks like the person who wrote it was just trying to create columns for each rpl_number.
I would think you could go with an inner join and then a case statement to replicate the data layout of the original query.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 38844610
index tables A B & C
that will increase your speed by 95 %
0
 
LVL 8

Author Comment

by:mrh14852
ID: 38844622
Right...each RPL must be in a separate column...do you have an example of how you would do it...my SQL knowledge only goes up to a certain level unfortunately.
0
 
LVL 8

Author Comment

by:mrh14852
ID: 38844627
All tables are indexed on their join fields.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 22

Expert Comment

by:plusone3055
ID: 38844631
0
 
LVL 8

Author Comment

by:mrh14852
ID: 38844644
Thanks...this is MySQL...even though I am sure the concepts are the same...however as I said all the tables are indexed.

The query WITHOUT the subqueries runs in 0.4 seconds.  WITH the subqueries is 5 seconds.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 38844646
0
 
LVL 8

Author Comment

by:mrh14852
ID: 38844655
So...you aren't reading...this isn't an issue of indexes...the indexes are there and it's very fast without the sub-queries....it's the sub-queries that's slowing it down....I am looking for a way to speed that up.
0
 
LVL 8

Author Comment

by:mrh14852
ID: 38844779
@jared_s

I followed your lead and figured it out...thanks for the direction!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now