"Jigal van Hemert" <[EMAIL PROTECTED]> writes: > From: "Scott Gifford"
[...] >> Right, ALL would be a great plan if it weren't for the LIMIT 1. > > The LIMIT 1 will be performed *after* the recordset is sorted :-( Ah, I think that is the piece I was missing. [...] >> 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. > > You have two LEFT JOINs, so the values of mls_num might be something (the > identical value you refer to) or NULL. So, you expect MySQL to evaluate the > JOIN conditions, figure out that the ORDER BY column actually is the same as > columns from other tables and see if one of these columns is actually the > first non-const table in the execution path? Of course with the LEFT JOINs it can be difficult to figure out (though in this case the JOINs are on mls_num, so it will never be NULL), but with two tables were using a "regular" join on the column in question, it does seem like a straightforward optimization. Maybe it's not common enough in practice to justify implementing it (or maybe I'm missing something). [...] >> 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. > There is no limiting condition whatsoever in the WHERE clause (except the > JOIN condition), so it will try to estimate based on the cardinality and the > number of records in the table which excecution path will require it to > evaluate the smallest number of records. It will try to estimate whether the > use of an index is appropriate or not. But I can imagine that it will favour > a full table scan of a small table that will result in a small recordset, > especially when it knows that the other, bigger tables are JOINed with only > 1 or a few records per join. In this case, the two tables have identical cardinality, so it shouldn't be favoring one over the other. [...] > This is part of a routine that generates many different queries based on a > wide variety of search forms, predefined selections, etc. Most of the time > MySQL does a great job with finding the 'right' execution path, but > sometimes it goes horribly wrong. That's been my experience too; we've been running this application for about 5 years, and this is only the second time something like this has come up. [...] >> I tried that using mirealsource_homes.mls_num in the WHERE clause and >> it didn't make a difference. > > I tried to say that you could/should try to limit the number of records that > are selected in the first place. [...] > So, can you think of a limitation that would preselect a part of the > mirealsource_homes.mls_num table? Unfortunately, not in the general case. >> 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 > > A full table scan is the right idea as long as you select more than 30% of > the table, sort it and after that apply the limit 10 (which is the only > solution for such a query). Right, but our queries always use a LIMIT 10 or LIMIT 1, so a full table scan is never a good deal if an index is available. I was hoping for some way to communicate that to MySQL. I think I'm going to take a look at the MySQL source and see if there's anything I can tweak to get the effect I want. I'll report back my results. Thanks for all your help! ----ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
