# Simple LEFT JOIN still uses file sort

**URL:** <https://forums.percona.com/t/simple-left-join-still-uses-file-sort/165>\
**Category:** Other MySQL® Questions\
**Created:** [January 8, 2007, 6:58pm UTC](https://forums.percona.com/t/simple-left-join-still-uses-file-sort/165 "2007-01-08T18:58:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Niels](https://avatars.discourse-cdn.com/v4/letter/n/ebca7d/32.png) [@Niels](https://forums.percona.com/u/Niels)\
**Post date:** [January 8, 2007, 6:58pm UTC](https://forums.percona.com/t/simple-left-join-still-uses-file-sort/165/1 "2007-01-08T18:58:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [January 9, 2007, 10:48am UTC](https://forums.percona.com/t/simple-left-join-still-uses-file-sort/165/2 "2007-01-09T10:48:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Niels](https://avatars.discourse-cdn.com/v4/letter/n/ebca7d/32.png) [@Niels](https://forums.percona.com/u/Niels)\
**Post date:** [January 10, 2007, 2:46pm UTC](https://forums.percona.com/t/simple-left-join-still-uses-file-sort/165/3 "2007-01-10T14:46:35Z")

</div>

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