It really isn't very complicated:
If you intend to fetch a single record to work on the most effective way to do that is a unique FIND statement. IMHO the Progress 4GL was successful compared to other 80s era tools precisely because they made this very, very simple and efficient:
The following is the heart and soul of quite a lot of business logic:
Code:
FIND customer NO-LOCK WHERE custNum = X no-error.
IF AVAILABLE( customer ) THEN
// do something
ELSE
// handle the error case
(An unfortunately large amount of code makes no attempt to handle the potential error.)
FIND FIRST is mostly an abomination coded by lazy slackers who are either blindly following local worst practices or who fundamentally misunderstand what the statement is actually doing. I'm sure you can find a few threads here and there where I go into detail about that.
Every excuse anyone makes about why they should always put FIRST on every FIND is bullshit and some of that bullshit is actively harmful to correct operation of the application.
If you are adding FIRST to every FIND you are doing it wrong.
Once in a while FIND FIRST makes sense. It is not, however, appropriate to glue FIRST onto every FIND.
====
FOR EACH is the statement that you should be using to process a set of records. That result set _could_ have zero, one or many records.
FOR FIRST is even more of an abomination than FIND FIRST. It does not do what you hope that it does - namely it does not sort and then select. The FOR statement selects and then sorts. FIRST means that it will select exactly one record. Which will result in a null sort. To see what kind of issues this might cause run the following in the sports database:
Code:
for each customer no-lock:
display custNum name discount.
end.
pause.
for first customer no-lock by discount:
display custNum name discount.
end.
FOR FIRST is a red flag. It almost certainly means that the programmer is wrong about something. If you're lucky the programmer left a comment behind confessing to his (or her) sins.
====
When speaking of efficiency index selection rules are critically important. The most vital rule is simple -- the compiler will choose the index with the most leading components that participate in equality matches. This also happens to be the most natural rule to think about when your database is properly designed following 3rd normal form. IOW: Every field in a record is a fact about the key, the whole key and nothing but the key. So help me Codd.
There are more rules and things can get quite complicated but, IMHO, that one rule covers 99.44% of actual usage and it will serve you very well.