Hi Keith, I would assume it's because LEFT JOIN forced a change in the join order (in EXPLAIN). Does using STRAIGHT JOIN give the same result?
So your query was this? SELECT a.field FROM table1 a LEFT JOIN table2 b USING (field2) ORDER BY b.field3 DESC If table1 is read first (which it should be), then I don't see how there's no temp table/filesort because the column(s) you're ordering by don't come from the first used table. Oh yeah, and is there an index on field2 in both tables? It'd be better to see the EXPLAIN output for the different queries. :-) It could also be an optimizer bug. What version of MySQL are you using? Matt ----- Original Message ----- From: "Keith Bussey" Sent: Monday, January 12, 2004 4:49 PM Subject: JOIN types > Hey all, > > I've read the pages in the MySQL manual that explain the types of JOINs many > times, but still think I'm missing something. > > I usually was always using INNER JOIN in all my join queries, and in a few > cases LEFT JOIN (in cases I wanted the rows in one table that were not in the > other one). > > I've started to discover, with the help of EXPLAIN, that the join type can > seriously affect performance. For example, I had a query such as this: > > SELECT a.field > FROM table1 a > INNER JOIN table2 b > USING (field2) > ORDER BY b.field3 DESC > > It was using both filesort and a temporary table (in EXPLAIN) and took about > 4.50 seconds to run. I switched the order of the tables in the join, putting > table2 first, and nothing changed in my EXPLAIN. > > I then changed the join to LEFT JOIN, and suddenly I had no more filesort or > temporary table, and the query took 0.05 seconds ! > > I compared that I got the same rows in my result both ways, and indeed I did. > > I'm positive I can improve many of my queries this way, but feel I need to > understand the JOINs better. If someone can point me to a guide on this > someplace, other than the mysql manual (as Ive already read it few times but > it didnt explain their differences and uses very well), I'd greatly appreciate > it =) > > Or, if you simply want to give the explanation yourself, that's fine too. > > Thanks, > > > -- > Keith Bussey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
