I have no problem using STRAIGHT_JOIN, etc. My problem is really just figuring out the optimal join order. Is doing a "SELECT COUNT" on each of the tables I'm going to join the way to do it? Isn't there potential for the count to take as long as the full query processing would take (especially since the attribute I'm doing a range on is not indexed)?
eric. On Sat, Oct 20, 2001 at 02:30:13PM +0300, Sinisa Milivojevic wrote: > On Fri, 19 Oct 2001 13:03:02 -0500 > Eric <[EMAIL PROTECTED]> wrote: > > > > Well, answering my own email, what I thought was a bug is not one at > > all. I was mistaken in thinking that MySQL paid any attention to the > > WHERE conditions when optimizing the join order beyond determining > > which keys are used for the join, correct? > > > > This is really terrible for queries like mine where the query could be > > sped up by orders of magnitude if the join optimizer would just > > determine which table in the join to scan and which to do the key > > lookup on based on a more intelligent estimation of the number of rows > > from each table. It would have to go beyond looking at what keys are > > used in the join (since each of the tables in my query can be looked > > up by the same key) and account for the WHERE conditions placed on the > > tables in the join. > > > > Is there sufficient metadata to estimate rows coming from a table > > based on conditions placed on the attributes of that table? Where is > > it? Has anyone ever thought of coding this? Can anyone give me a > > place to start? > > > > eric. > > HI! > > At least, with LEFT JOIN you can always specify precisely which table is to be >scanned and which to be searched by key. > > In other joins, you can use STRAIGHT_JOIN with correct order of tables in order to >achieve the same. > > Yes, the above is little bit harder to be done with dynamically created queries, but >it is still possible, as you can always get number of rows. > > -- > > Regards, > > -- > For technical support contracts, go to https://order.mysql.com/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus > <___/ www.mysql.com -- _____ _ | ____|(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |______/ | or by mailing [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
