Dyego, ----- Alkuperäinen viesti ----- Lähettäjä: "Dyego Souza Dantas Leal" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Tuesday, March 02, 2004 3:09 PM Aihe: Re[2]: InnoDB or MySQL error ?
> Heikki: > > Hi, > > I would like to explain what is going on with the straight_join > when it is used with my database. > > Suppose the command bellow: > > explain select table1 left join table2 on A=B left join table3 on > C=D where ID = 1; > > When it is executed on one of our servers, mySql uses the right > order for the search. It indicates the order Table1, table2 and > table3, showing the indexes for each one. > > When it is executed on another of our servers, with the same > database, but with another data, mySql indicates a non-optimized > order for the search, and do not use the appropriated indexes, > using sequential search. It can indicate, for example, table3, > table1 and table2. > > To solve it I tried to modify the command, to use the > straight_join, that is intended to force mySql to use the order I > used to write the command: > > explain select STRAIGHT_JOIN table1 left join table2 on A=B > left join table3 on C=D where ID = 1; > > But it got no success is this case. I'd already used it with > success many times, but using it in this specific database > and in this specific situation, the Straight_Join didn't work. > > I think it would be great if you could do something, because it can > be a mySql bug, or innoDb bug instead. Because this, I sent to you > an e-mail with the exact command I used and with the database I > use. With that you will be able to reproduce the bug, as I could > before sending it to you. please file a bug report to bugs.mysql.com. This really cannot be an InnoDB bug because InnoDB does not determine the join order. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html > Tuesday, March 2, 2004, 10:02:06 AM, você escreveu: > > ---------------[inicio]------------------ > > HT> Dyego, > > HT> http://www.mysql.com/doc/en/JOIN.html > > HT> LEFT JOIN means an 'outer join'. I think it does NOT force the join order. > > HT> STRAIGHT JOIN forces the join order. > > HT> Best regards, > > HT> Heikki > HT> Innobase Oy > HT> http://www.innodb.com > HT> InnoDB - transactions, row level locking, and foreign keys for MySQL > HT> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > HT> tables > > ---------------[cortar]------------------ > > > InnoDB,MySQL,SQL,Query > ------------------------------------------------------------------------- > ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento > ------------------------------------------------------------------------- > E S C R I B A I N F O R M A T I C A > ------------------------------------------------------------------------- > The only stupid question is the unasked one (somewhere in Linux's HowTo) > Linux registred user : #230601 > -- ICQ : 1647350 > $ look into "my eyes" Phone : +55 041 2106-1212 > look: cannot open my eyes Fax : +55 041 296 -6640 > ------------------------------------------------------------------------- > Reply: [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]