Not entirely sure I answer the question, but I'll give it a try.
Conditions on different tables
------------------------------
Table t1: k1, nk1, nk2
Table t2: k2, nk3
where k* are indexed columns and nk* are non-indexed.
SELECT t1.*
FROM t1 join t2 on nk2=k3
WHERE ...
Assume join order t1,t2
* With conditions on k1 only, MySQL is able to filter rows by looking at the k1 index alone. All rows read from t1 qualify to be in the result set (if there is a join match in t2).
* With conditions on k1 and nk1: The index cannot be used to evaluate the condition on nk1, so some of the records read from t1 may be discarded even before looking for join matches in t2. This is more expensive than filtering in the index because non-qualifying rows are read from the table.
* With conditions on k1 and nk3: Only qualifying rows from t1 are read from the table. Matching rows are looked up in the index of k3 and the matching rows are read from t2 and joined with the row from t1. The condition on nk3 is not evaluated until this point. This is more expensive than the condition nk1 because even if the nk3 condition is false for a certain row, MySQL has to find the matching row in the k2 index and then read the row from t2.
Hence, when two conditions are equally selective, evaluating the condition early in the join order is cheaper than evaluating it later. Also, evaluating a condition in an index is cheaper than evaluating it on the row.
ORDER BY and LIMIT
------------------
There are three ways MySQL can provide ordering:
1) Order is performed on columns in the first table in the join order. Two possibilities:
1.1) The index used to access the table provides the ordering. Ordering is "free" in this case. With LIMIT, MySQL can stop as soon as <limit> number of joined rows have been returned.
1.2) Otherwise, the first table can be sorted before joining. With LIMIT, MySQL can stop processing as soon as <limit> number of rows have been returned, but the entire first table has already been read to be able to do the sorting (so this is more expensive than 1.1)
2) Order is performed on columns from tables later in the join order. In this case ordering is done after performing the entire join. Depending on join matches and conditions, there may be fewer or more rows to sort than with strategy 1.2. LIMIT will not reduce the cost of this query significantly because all condition evaluation, row retrieval and joining as been done anyway.
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com