mrh14852
asked on
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.
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!
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 NULLShouldn't I be able to do an inner join instead of sub queries...but still retain the full result set from table_a?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
All tables are indexed on their join fields.
Full Tutorial with screenshots :)
http://www.codeproject.com/Articles/39006/Overview-of-SQL-Server-2005-2008-Table-Indexing-Pa
http://www.codeproject.com/Articles/39006/Overview-of-SQL-Server-2005-2008-Table-Indexing-Pa
ASKER
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.
The query WITHOUT the subqueries runs in 0.4 seconds. WITH the subqueries is 5 seconds.
ASKER
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.
ASKER
@jared_s
I followed your lead and figured it out...thanks for the direction!
I followed your lead and figured it out...thanks for the direction!
that will increase your speed by 95 %