Simple LEFT JOIN still uses file sort

MySQL & MariaDB Other MySQL® Questions
1

Hi!

I’m using a very simple JOIN:

SELECT *
FROM A
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?

Thanks,
Niels

2

26

This query basically looks up a value which is located in a range between 2 values in one record.

Any ideas how to speed it up?

Thanks!I’m just q

3

----±------------±------±-----±---------------±------- --------±--------±--------------------±-------±--------- —+
| 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |
| 1 | SIMPLE |