Why is below 2 query generating different execution plan in MYSQL? Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for 1st query it does not require filesort.
Apart from id I have index on c.table_b_id and c.some_indexed_id
SELECT * FROM TableA a 
inner join TableB b on a.id = b.id 
inner join TableC c on c.table_b_id = b.id
where c.some_indexed_id = 1
order by c.table_b_id asc;



SELECT * FROM TableA a 
inner join TableB b on a.id = b.id 
inner join TableC c on c.table_b_id = b.id
where c.indexed_id = 1
order by b.id asc;
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 &amp; where c.table_b_id requires sorting.