Sorry, here are the EXPLAINS of a similar case, where what I did was switch the first two tables in the join, and make it LEFT not INNER (note: the query may look a little odd, as I took out some of the fields I was selecting, etc...to make it shorter ;p)
mysql> EXPLAIN SELECT t.profile_alias FROM iwantu_tables_2 t INNER JOIN iwantu_profile_2_1 p USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +-------+--------+------------------------+---------+---------+---------------- --+---------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+------------------------+---------+---------+---------------- --+---------+----------------------------------------------+ | d | index | idx_uid | idx_uid | 3 | NULL | 1002592 | Using index; Using temporary; Using filesort | | p | ref | idx_profile_status,uid | uid | 3 | iwantu_new.d.uid | 1 | Using where | | t | eq_ref | PRIMARY | PRIMARY | 3 | iwantu_new.p.uid | 1 | | | s | eq_ref | PRIMARY | PRIMARY | 4 | p.description_id | 1 | Using index | +-------+--------+------------------------+---------+---------+---------------- --+---------+----------------------------------------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT t.profile_alias FROM iwantu_profile_2_1 p LEFT JOIN iwantu_tables_2 t USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +-------+--------+--------------------+--------------------+---------+--------- ---------+--------+-----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+--------------------+--------------------+---------+--------- ---------+--------+-----------------------------+ | p | range | idx_profile_status | idx_profile_status | 4 | NULL | 684324 | Using where; Using filesort | | t | eq_ref | PRIMARY | PRIMARY | 3 | iwantu_new.p.uid | 1 | | | d | ref | idx_uid | idx_uid | 3 | iwantu_new.t.uid | 1 | Using index | | s | eq_ref | PRIMARY | PRIMARY | 4 | p.description_id | 1 | Using index | +-------+--------+--------------------+--------------------+---------+--------- ---------+--------+-----------------------------+ 4 rows in set (0.00 sec) Also, I'm running MySQL 4.0.13-standard, STRAIGHT JOIN doesnt seem to exist for me ;p -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager +50 6 280 2284 ext.108 Quoting Matt W <[EMAIL PROTECTED]>: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
