The explain depends on which column you using in WHERE clause, the column which are large in size that which doesn't fit in SORT BUFFER requires filesort for sorting purpose. And other point is b.id may be primary key which is sorted by default, So it doesn't requires to do sorting & where c.table_b_id requires sorting.
Mike D. has provided an answer on how SQL Server sorts data that might help you understand what is it about the deterministic result you see on some comments.
The Arguments of the ORDER BY doc says:
ASC | DESC Specifies that the values in the specified column should be
sorted in ascending or descending order. ASC sorts from the lowest
value to highest value....
I wouldn't worry about hardcoding the number of months in a year
create table #t (mm int)
insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
select mm, (mm + 8) % 12 from #t order by 2 asc
I did a lot of experimentation by checking the performance. It may help others.
The fastest way (by far) is to do the random rearrangement outside the SQL query.
SELECT ID INTO OUTFILE '/tmp/id.csv'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM t1
shuf -o /tmp/id.csv < /tmp/id.csv
and finally the fast INSERT step
LOAD DATA LOCAL ...
Create intermediate table. Copy your ID from t1 into it. Add virtual generated column which calculates some hash from id value, and index it. Use this table as a source for insertion, add sorting by created index expression, and force it (without index hint it may be ignored due to 100% rows selection.. from the other side, it must be used ...