Solved

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

Posted on 2013-02-01
9
253 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 8

Author Comment

by:mrh14852
ID: 38844627
All tables are indexed on their join fields.
0
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

813 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

14 Experts available now in Live!

Get 1:1 Help Now