> Is JOIN faster than LEFT JOIN, or is this because LEFT JOIN will usually result in more rows in the resulting set of data?
They are different. LEFT JOIN includes rows missing from the _right_ query; JOIN excludes them. They are different speeds because the optimizer can start with either table for JOIN, but may be restricted to looking at the _left_ table first in a LEFT JOIN. You noticed in the EXPLAINs that that happened.
Notice that "SELECT ... FROM rmm_sales_order LEFT JOIN ( SELECT ... )" was forced to do table scans on the subquery (since there were no indexes on the subquery). 5.6 will discover and provide an index. Meanwhile, a workaround is to do
CREATE TEMPORARY foo ( INDEX (...) ) SELECT ...;
SELECT ... FROM rmm_sales_order LEFT JOIN foo ON ...;
to create a temp table with an index, and then use it.
The clue to the above was
*| 1 | PRIMARY | rmm_sales_order | ALL | 5127 |
*| 1 | PRIMARY | <derived2> | ALL | 6874 |
versus
*| 1 | PRIMARY | <derived2> | ALL | 6874 |
*| 1 | PRIMARY | rmm_sales_order | eq_ref | 1 |
I suspect there are more cases where a temp table with an index would help.
If LEFT JOIN gives you the 'correct' resultset, then, by all means, use LEFT JOIN.
> float(10,2)
Do not use (M,N) on FLOAT. It does not work like you expect, and can lead to roundoff errors. Instead, use DECIMAL(10,2).