Thanks for your response, Jigal. More below... "Jigal van Hemert" <[EMAIL PROTECTED]> writes:
> From: "Scott Gifford" [...] >> 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). Right, ALL would be a great plan if it weren't for the LIMIT 1. >> 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. I'm a little surprised MySQL can't figure out on its own that it can get the same effect by using mls_num from either table, since the tables are joined on it, so the values will always be identical. > 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. It looks to me like it's actually 100 * 1 * 1 * 1 = 100 (8 is the key length), which is the same as what I get when I force a STRAIGHT_JOIN. So with two plans that will use the same number of records, I'm surprised MySQL doesn't choose the one that will allow it to use an index. > 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. I know there are a couple of tricks like that to fix this one query. What makes this hard is that that query is one of many that can be generated by a CGI-to-SQL search gateway. If I put in a FORCE INDEX (mls_num) and the user searches by price, for example, query performance will be very bad. In order to know the right indexes to force, as far as I can tell I'd have to implement my own optimizer, which seems somewhat excessive. [...] > 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 tried that using mirealsource_homes.mls_num in the WHERE clause and it didn't make a difference. > 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. I tried that too, and it didn't make a difference (though I agree it is clearer). I also tried installing a copy of 4.1.12-standard to see if the situation was better, but the results were identical. And, I tried setting "max_seeks_for_key" to a low number (I tried 100, 10, 1, and 0), which is supposed to discourage tables scans, and that didn't help. Are there any other global things I can try, to tell MySQL to avoid table scans? The queries almost always use at most LIMIT 10, so a table scan is generally not the right idea ----Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
