On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo <new2mys...@gmail.com>wrote:
> > Here is the structure of the Table T1 ( ENGINE=InnoDB ) - > > `c1` varchar(128) NOT NULL default '', > `c2` int(11) NOT NULL default '0', > `c3` varchar(32) NOT NULL default '', > `c4` blob, > `c5` double default NULL, > `c6` varchar(255) default NULL, > `c7` enum('BLOB','NUMERIC','STRING') NOT NULL default 'BLOB', > KEY `key1` (`c1`,`c2`,`c3`,`c5`), > KEY `key2` (`c1`,`c2`,`c3`,`c6`), > > The query is as below - > SELECT * FROM T1 WHERE (c1 = '$string1' AND c2 IN ($int1, $int2 , $int3, > $int4 , $int5, $int6 , $int7 , $int8, $int9, $int10 )) > > Mysql is choosing key1 & key2 in different situations . > > > > > > On Sun, Jul 11, 2010 at 6:39 PM, Prabhat Kumar <aim.prab...@gmail.com>wrote: > >> You you send us explain of that query. >> >> >> On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya < >> new2mys...@gmail.com> wrote: >> >>> optimize / analyze table in each of the hosts is not a good option for me >>> , >>> this is an in-production set-up with minimal number of boxes in rotation >>> . >>> >>> >>> >>> On Sun, Jul 11, 2010 at 5:57 PM, arijit bhattacharyya >>> <new2mys...@gmail.com>wrote: >>> >>> > >>> > Hi , i am running into trouble due to wrong index chosen by mysql in >>> some >>> > particular type of queries . This is happening in a critical production >>> > environment where we have deployment in two different colocations . I >>> am >>> > seeing that a paticular query is using one index in one set of hosts & >>> > another index in another set of hosts . We are not using 'use index' >>> clause >>> > to explicitly mention the index due to some limitations . But wondering >>> > based on what mysql is using different indexes in different hosts . >>> This is >>> > really surprising , since dataset & table structures are exactly same >>> in all >>> > the hosts . >>> > >>> > Other than changing the code to force using 'use index' , is there any >>> > other way to resolve it ? And what's the exact reason behind this ? >>> Just to >>> > have mysql choose the correct index always will simply solve my problem >>> . >>> > >>> > Thanks . >>> > >>> >> >> >> >> -- >> Best Regards, >> >> Prabhat Kumar >> MySQL DBA >> Datavail-India Mumbai >> Mobile : 91-9987681929 >> www.datavail.com >> >> My Blog: http://adminlinux.blogspot.com >> My LinkedIn: http://www.linkedin.com/in/profileprabhat >> > >