From: "Scott Gifford" > mysql> explain > SELECT mirealsource_homes.mls_num, > mirealsource_homes_supplemental.listdate, > mirealsource_images.image1, > mirealsource_homes_stats.detail_views > FROM mirealsource_homes, > mirealsource_homes_supplemental > LEFT JOIN mirealsource_images > ON mirealsource_homes.mls_num = > mirealsource_images.mls_num > LEFT JOIN mirealsource_homes_stats > ON mirealsource_homes.mls_num = > mirealsource_homes_stats.mls_num > WHERE > mirealsource_homes.mls_num = > mirealsource_homes_supplemental.mls_num > ORDER BY mirealsource_homes.mls_num > LIMIT 1; > > +----+-------------+---------------------------------+--------+------------- --+---------+---------+-----------------------------------------------+----- -+---------------------------------+ > | id | select_type | table | type | > possible_keys | key | key_len | ref > | rows | Extra | > +----+-------------+---------------------------------+--------+------------- --+---------+---------+-----------------------------------------------+----- -+---------------------------------+ > | 1 | SIMPLE | mirealsource_homes_supplemental | ALL | > PRIMARY | NULL | NULL | NULL > | 100 | Using temporary; Using filesort | > | 1 | SIMPLE | mirealsource_homes | eq_ref | > PRIMARY | PRIMARY | 8 | > devel.mirealsource_homes_supplemental.mls_num | 1 | | > | 1 | SIMPLE | mirealsource_images | eq_ref | > PRIMARY | PRIMARY | 8 | devel.mirealsource_homes.mls_num > | 1 | | > | 1 | SIMPLE | mirealsource_homes_stats | eq_ref | > PRIMARY | PRIMARY | 8 | devel.mirealsource_homes.mls_num > | 1 | | > +----+-------------+---------------------------------+--------+------------- --+---------+---------+-----------------------------------------------+----- -+---------------------------------+ > > Apparently MySQL's optimizer sees that it can use the primary key for > mirealsource_home_supplemental to do the query, but for some reason > decides not to.
This is often the case when the query will probably return more than 30% of the records in that table. In such cases it is more efficient to do a full table scan (which is indicated here by the 'ALL' type in the explain output). > I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by > "mirealsource_homes_supplemental.mls_num" instead. However, this > query is part of a larger framework that handles a wide variety of > queries, so I need to understand why this is happening instead of > tweaking individual cases. Furthermore MySQL can only use an index for sorting if all columns in the ORDER BY clause are from the first table in the explain output that doesn't have a 'const' join type. This is why setting the ORDER BY to mirealsource_homes_supplemental.mls_num will remove the 'Using filesort' and result in faster sorting. MySQL tries to optimize queries by (among others) guestimating which path will result in the smallest number of records. It appears that this path with 100 * 8 * 8 * 8 (51200) records is the minimum size it can achieve. You can use USE INDEX to (strongly) suggest the use of an index to MySQL and see if it speeds up the query. Using FORCE INDEX will tell MySQL that a full table scan is very expensive, so this will make it extremely unlikely that it will not use the index. The optimizer often seems to gues 'wrong' in terms of speed. I put a USE INDEX in a query and consequently the guessed number of records was increased from around 1000 to over 2000, but since it could now use the index to sort the result set the query was way faster in the end. In this query you want the data where mls_num is as small as possible. Is there a way you can limit the number of records by using an extra where condition? This way you may change the order of the tables and make the query faster. I would also move the current WHERE condition to an ON condition in the FROM part as it is not meant to limit the selected records, but as a definition on how to join the two tables. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
