MySQL Forums
Forum List  »  Performance

subselect in 'IN list', Win2000, mysql 4.1.7
Posted by: Threepwood Guybrush
Date: December 01, 2004 10:47AM

Hi,
I wonder about the performance of the following subselect:

----------------------------------------------------------------------
SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND (sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(SELECT DISTINCT d2.ID FROM DBRAMM AS d2 WHERE d2.real_dbramm_id = 216)))
----------------------------------------------------------------------
[requires minutes]

compared to the identical query without subselect:

----------------------------------------------------------------------
SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND (sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(1,34,15,36,324,...some more))
----------------------------------------------------------------------
[requires 8 seconds]

the sqls are the same, necessary indexes are created...the only difference is the IN list via subselect vs given as predifined id-list, like used in the pre-subselect times of mysql.
can anyone tell me the difference or how I can get this run faster?

many thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
subselect in 'IN list', Win2000, mysql 4.1.7
3635
December 01, 2004 10:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.