• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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!
0
mrh14852
Asked:
mrh14852
  • 5
  • 3
1 Solution
 
Jared_SCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now