On Tue, Dec 18, 2001 at 11:19:06PM +0200, Heikki Tuuri wrote: : Philip, : : I recommend using the MySQL STRAIGHT JOIN and USE INDEX clauses to manually : force the best plan.
I'd love to, but I also have to maintain a spec of database agnosticism (which I could do in code, of course, but I shouldn't be forced into MySQL-specific SQL when perfectly valid SQL should [and sometimes does] work just as well). That still doesn't rectify the situation that InnoDB queries aren't being properly optimized, and I have seen it on other databases under other circumstances. : You could also use innodb_table_monitor to check that the key value set : cardinality estimates are approximately right for the tables h, pt, and p. The cardinality estimates are accurate, which makes it all the more baffling how InnoDB/the optimizer are arriving at these conclusions. : But to put it the other way, how could the optimizer know beforehand the : cardinality of the h,p partial join versus the pt,p partial join? You're right, the optimizer can't know beforehand the cardinality of the h,p vs pt,p partial join, *but* that cardinality shouldn't be changing, especially not on tables whose index fields aren't being updated. Records in those tables are being affected, yes, but those specific column entries aren't, so the h,p vs. pt,p cardinality should never be an issue once the optimal plan has been found. That's why, under MyISAM, you issue an ANALYZE TABLE on your table(s) after you've populated them with a good chunk of your data and then you generally don't need to worry about it again. Unless your data set radically shifts and you absolutely need to switch to another index, your optimizer plan doesn't need to change. ANALYZE TABLE isn't an option under InnoDB (although sometimes, it appears to help). This stuff is shifting back and forth, and it's *VERY FAR* off when it shifts the wrong way. My question is "Why?" * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
