I’m using a very simple JOIN:
LEFT JOIN B ON A.pointer=B.id
ORDER BY A.id;
EXPLAIN tells me it uses file sort. There are indexes on all fields. The problem is the ORDER, but I don’t fully understand why this situation causes problems.
I use this kind of query a lot in a webapp I’m doing, and for small tables it’s fine. But with 100k records in A and B, it becomes unusable. Maybe I could fine tune a cache or two, but that doesn’t really solve the problem. Can it really be that it’s impossible to do this without file sort?