Innodb Choosing Random Index

--005045013c62c11134048b1bc038
Content-Type: text/plain; charset=ISO-8859-1

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 .

--005045013c62c11134048b1bc038--

Re: Innodb Choosing Random Index

--0016e6d36948d08ba0048b1c3de9
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d36948d08ba0048b1c3de9--

Re: Innodb Choosing Random Index

--00148504393129de80048b1c5a29
Content-Type: text/plain; charset=ISO-8859-1

You you send us explain of that query.

On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya
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
> 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

--00148504393129de80048b1c5a29--

Re: Innodb Choosing Random Index

--00221534d4c7eaee40048b1cc90c
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo 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 wrote:
>
>> You you send us explain of that query.
>>
>>
>> On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya <
>> new2mysql9@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
>>> 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
>>
>
>

--00221534d4c7eaee40048b1cc90c--

Re: Innodb Choosing Random Index

In the last episode (Jul 11), Leonardo Leonardo said:
> On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo 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 .

InnoDB estimates index cardinality on every query by examining a few random
disk blocks in each index. Depending on which pages are examined, mysql
might decide one index is better then the other even if everything else is
the same. Try running EXPLAIN SELECT on the same query a few times and see
if the optimizer picks different indexes. If you're running a new enough
version of mysql (5.1.38 or newer), you can change the
innodb_stats_sample_pages variable to raise the number of pages from the
default of 8 (try 16). That will make the estimate more accurate and
hopefully mysql will pick the right index consistently.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.htm l#sysvar_innodb_stats_sample_pages

Another solution might be to create another index on (c1,c2), since that
index is the most efficient one for your particular query. MySQL will
always pick that index over the other two.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org