Re: Query Efficiency
Hi Erik,
AFAICT, the two queries are not equivalent since the result of the latter will have one row per customer, while the former will give as many rows as are in the table. (Maybe there should be a GROUP BY CustID, CustName for the fist subquery).
As for efficiency, I cannot talk for other database, but the latter one will be most efficient in MySQL since it can be executed with a single table scan. For the former, MySQL would use one table scan per subquery in addition to the join processing. Also, if MySQL 5.5 or earlier versions are used, no indexes will be available when doing the join. Hence, the complexity of the join would be c^e where c is the number of customers, and e is the number of errors. That part is solved in MySQL 5.6.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway
Subject
Views
Written By
Posted
2450
September 20, 2013 06:30AM
917
September 20, 2013 11:04AM
Re: Query Efficiency
963
September 25, 2013 04:29AM
780
October 01, 2013 08:42AM
875
October 01, 2013 08:53AM
812
September 30, 2013 06:13PM
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.