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
>>
>
>
