Announcement

Announcement Module
Collapse
No announcement yet.

Simple LEFT JOIN still uses file sort

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Simple LEFT JOIN still uses file sort

    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

    Comment


    • #3
      ----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+
      | 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |
      | 1 | SIMPLE |

      Comment


      • #4
        V | ref | Index__avtalid | Index__avtalid | 5 | carro.avtal.avtalid | 138929 | Using where |
        +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+
        2 rows in set (0.00 sec)
        This query takes 15 s


        If I ig

        Comment

        Working...
        X