optimizer bug in the index used by mysql/Innodb in the search

am 26.02.2003 19:30:39 von rafarife

Description:
Hello,

I am working with mysql/InnoDb 4.0.11 under Windows 2000.

I have the following table:

CREATE TABLE `giros` (
`Doc` varchar(10) NOT NULL default '',
`Tipo` char(1) NOT NULL default '',
`NRe` varchar(5) NOT NULL default '',
`FoP` char(2) NOT NULL default '',
`FeL` date default NULL,
`FeV` date default NULL,
`NRm` varchar(6) NOT NULL default '',
`Pis` char(2) NOT NULL default '',
`Imp` decimal(20,4) NOT NULL default '0.0000',
`Cod` varchar(6) NOT NULL default '',
`Nom` varchar(40) NOT NULL default '',
`Dir` varchar(35) NOT NULL default '',
`Cop` varchar(6) NOT NULL default '',
`Pob` varchar(30) NOT NULL default '',
`Pro` varchar(25) NOT NULL default '',
`BCd` varchar(8) NOT NULL default '',
`BNm` varchar(40) NOT NULL default '',
`BDr` varchar(35) NOT NULL default '',
`BPb` varchar(30) NOT NULL default '',
`BPr` varchar(25) NOT NULL default '',
`Cba` varchar(10) NOT NULL default '',
`Dct` char(2) NOT NULL default '',
`NLe` text,
`Cob` tinyint(4) unsigned NOT NULL default '0',
`Ctb` tinyint(4) unsigned NOT NULL default '0',
`BCp` varchar(6) NOT NULL default '',
`Impreso` tinyint(4) unsigned NOT NULL default '0',
`Est` varchar(20) NOT NULL default '',
`Linea` int(11) NOT NULL default '0',
PRIMARY KEY (`Tipo`,`Doc`,`NRe`),
KEY `NRmLinea` (`NRm`,`Linea`),
KEY `TipoFeVCod` (`Tipo`,`FeV`,`Cod`),
KEY `CodTipoDocFeV` (`Cod`,`Tipo`,`Doc`,`FeV`)
) TYPE=InnoDB;

I ran the following query:

SELECT TIPO,DOC,NRE
FROM GIROS
WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE<'01/01')
OR (GIROS.TIPO='R' AND GIROS.DOC<'ZA03003996') OR GIROS.TIPO<'R'
ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC LIMIT 1

Time: 0.20 secs.
Explain:
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort

I changed the table type to MyIsam (alter table giros type=MyIsam),
and I ran the above query:

Time:0.02 secs
Explain:
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index

So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses
a wrong index (TipoFeVCod).

Thanks in advance,
Rafa

How-To-Repeat:
Select ... from giros ...

Fix:
-

Synopsis:optimizer bug in the index used by mysql/Innodb in the search

Submitter-Id:
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.11 Gamma(InnoDB)

Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System: Windows 2000
Compiler: -
Architecture: i

____________________________________________________________ ______
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

------------------------------------------------------------ ---------
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 bugs-thread13844@lists.mysql.com
To unsubscribe, e-mail

Re: optimizer bug in the index used by mysql/Innodb in the search

am 26.02.2003 23:56:16 von Alexander Keremidarski

Hello,

rafarife@netscape.net wrote:

> Description:
> Hello,
>
> I am working with mysql/InnoDb 4.0.11 under Windows 2000.
>
> I have the following table:

> Time: 0.20 secs.
> Explain:
> table type possible_keys key key_len ref rows Extra
> GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort
>
> I changed the table type to MyIsam (alter table giros type=MyIsam),
> and I ran the above query:
>
> Time:0.02 secs
> Explain:
> table type possible_keys key key_len ref rows Extra
> GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index
>
>
> So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses
> a wrong index (TipoFeVCod).

From EXPLAIN result output you can do only opposite conclusion:

With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match
9417 rows. This is about twice less rows than expected with PRIMARY key in second
explain 19472

> How-To-Repeat:
> Select ... from giros ...
>
> Fix:

Sorry but this is not Repeatable Test Case.

Optimizer issues highly depend on your real data.

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: optimizer bug in the index used by mysql/Innodb in the search

am 26.02.2003 23:56:16 von Alexander Keremidarski

Re: optimizer bug in the index used by mysql/Innodb in the search

am 28.02.2003 14:42:37 von Peter Zaitsev

On Thu, 2003-02-27 at 01:56, Alexander Keremidarski wrote:
> Hello,
>
> rafarife@netscape.net wrote:
>
> > Description:
> > Hello,
> >
> > I am working with mysql/InnoDb 4.0.11 under Windows 2000.
> >
> > I have the following table:
>
>
> > Time: 0.20 secs.
> > Explain:
> > table type possible_keys key key_len ref rows Extra
> > GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort
> >
> > I changed the table type to MyIsam (alter table giros type=MyIsam),
> > and I ran the above query:
> >
> > Time:0.02 secs
> > Explain:
> > table type possible_keys key key_len ref rows Extra
> > GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index
> >
> >
> > So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses
> > a wrong index (TipoFeVCod).
>
> From EXPLAIN result output you can do only opposite conclusion:
>
> With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match
> 9417 rows. This is about twice less rows than expected with PRIMARY key in second
> explain 19472

Actually it does not counts as en explanation unfortunately,
note it has ORDER BY DESC LIMIT 1,

This shall increase the chance for primary key to be used as it allows
to retrieve rows in specific order.
Actually in such case it is almost always good idea to use such plan,
with only exception if where clause is very selective so scanning over
primary key will result in many rows to be analyzed before where
condition is met, and it is rather hard to guess it.

Basically it is not the bug but optimizer in-optimality - you can use
USE INDEX or FORCE INDEX to make sure MySQL uses the index which is
optimal.

Of course we would really like Optimizer to select optimal case
automatically, so we would appreciate if you would provide complete test
case for this problem, so we can investigate it.

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread13858@lists.mysql.com
To unsubscribe, e-mail