Solved

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

Posted on 2013-02-01
9
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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