| Simple LEFT JOIN still uses file sort [message #551] |
Mon, 08 January 2007 19:28  |
 Niels
Messages: 2
Registered: January 2007 |
Junior Member |
|
|
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
|
|
|
|
|
| Re: Simple LEFT JOIN still uses file sort [message #576 is a reply to message #575 ] |
Wed, 10 January 2007 16:12  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
