MySQL Forums
Forum List  »  Quality Assurance

Optimal SELECT(s) in stored procedures
Posted by: Marek Erban
Date: October 30, 2012 02:48AM

Look at two following cases.
What use of SELECT is more optimal and faster (and why)?
(all is in stored procedure, all conditions use right indexes, SELECT from A always returns one row, SELECT from B or C can return more rows - not in this "INTO" case but in case of using CURSORs)

Case 1:
SELECT F1 INTO L1 FROM A WHERE K1 = 123;
SELECT F2 INTO L2 FROM B WHERE K2 = L1;
or
SELECT F2 INTO L2 FROM A, B WHERE (A.K1 = 123) AND (B.K2 = A.F1);
?

Case 2:
SELECT F1 INTO L1 FROM A WHERE K1 = 123;
SELECT F2 INTO L2 FROM B WHERE K2 = L1;
SELECT F3 INTO L3 FROM C WHERE K3 = L1;
or
SELECT F2 INTO L2 FROM A, B WHERE (A.K1 = 123) AND (B.K2 = A.F1);
SELECT F3 INTO L3 FROM A, C WHERE (A.K1 = 123) AND (B.K3 = A.F1);
?

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimal SELECT(s) in stored procedures
1974
October 30, 2012 02:48AM


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.