Does Ordering BY primary key help query performance when joining table to query Wondering about performance when joining a table with a query. Does the ORDER BY help?
For example if I am joining a user_stat table where the primary key is user_id, order_type with a selection of orders.
SELECT user_stats.*, order_stat.num_orders as orders_in_range FROM
  user_stats INNER JOIN (
    SELECT user_id , order_type, count(order_id) as num_orders FROM orders 
    WHERE order_date &gt; '2020-01-01 00:00:00' 
    GROUP BY user_id, order_type
    !!! ORDER BY user_id, order_type !!!
  ) as order_stat ON user_stats.user_id = order_stat.user_id AND user_stats.order_type = order_stat.order_type 

I understand that there are other ways to write this. I am just wondering if the ORDER BY speeds things up, slows them down or has no effect. Or if there is some way to let the optimizer know that they are in order so can do things faster?
I am using mySQL 5.7
In general, specifying an ORDER BY clause is an extra operation that can result (and generally does) in more work for your queries, even queries that JOIN two tables together with an ORDER BY clause that sorts the data to the order of the JOIN predicates.
The query optimizer is (usually) smart enough to know when to internally sort the data to most efficiently JOIN two tables together, and will do so dependent on the indexes of those tables (because indexing already sorts the data) that it's going to use for the query.
So it's theoretically possible that the query optimizer might choose an index that is sorted differently (and/or results in a different sort operation) than the one you specified in your ORDER BY clause which will result in extra work for the query to be processed and the data to be served. And in the best case, generally, you're not adding any benefit by specifying an unneeded ORDER BY clause even if it's not causing any harm.