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

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!
LVL 8
mrh14852Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
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
 
plusone3055Commented:
index tables A B & C
that will increase your speed by 95 %
0
 
mrh14852Author Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mrh14852Author Commented:
All tables are indexed on their join fields.
0
 
plusone3055Commented:
0
 
mrh14852Author Commented:
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
 
plusone3055Commented:
0
 
mrh14852Author Commented:
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
 
mrh14852Author Commented:
@jared_s

I followed your lead and figured it out...thanks for the direction!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.