Version: 3.23.33 I'm experiencing a problem... Whenever I use LEFT JOIN in my SELECT statement, it no longer appears to use the index to sort, hence making the query slow. I have two tables: "files" (~3000 rows) and "folders" (~500 rows). In the tables, the "id" columns are the primary keys, and all other columns accessed in the SELECT statements are single-column indexed. *** Before adding LEFT JOIN *** SELECT files.id FROM files ORDER BY files.date LIMIT 1; 1 row in set (0.00 sec) EXPLAIN says: +-------+-------+---------------+------+---------+------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+------+---------+------+------+-------+ | files | index | NULL | date | 8 | NULL | 2936 | | +-------+-------+---------------+------+---------+------+------+-------+ *** After adding LEFT JOIN *** SELECT files.id FROM files LEFT JOIN folders ON files.folder=folders.id ORDER BY files.date LIMIT 1; 1 row in set (0.04 sec) EXPLAIN says: +---------+--------+---------------+---------+---------+--------------+----- -+----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+--------+---------------+---------+---------+--------------+----- -+----------------+ | files | ALL | NULL | NULL | NULL | NULL | 2936 | Using filesort | | folders | eq_ref | PRIMARY | PRIMARY | 4 | files.folder | 1 | Using index | +---------+--------+---------------+---------+---------+--------------+----- -+----------------+ Notice how the second statement is taking many times longer to execute, apparently because it's using filesort instead of the index. What, if anything, am I doing wrong here? Isn't there some way I can optimize the second query to be nearly as fast as the first one? I tried adding "USE INDEX (date)" but that had no effect. I've read over the "optimization" part of the MySQL manual many times, and read reports of similar-sounding problems in the mailing list archive, but I can't seem to find a working solution... Thanks. Jordan Russell --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
