optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb

optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb

am 09.10.2002 11:56:47 von rafarife

Description:
Hello,

We reported a problem about selecting fields that some of them didn´t belong to
the used index on September.

We have already installed MySql 4.0.4 and we have a similar problem
(and also we are still having the original problem) when we select fields which some of them
don´t belong to the primary index that MySql should use in the search. In this query,
we haven´t any OR condition in the WHERE clause as we had in the query that we made
on September. Moreover, in this case MySQL doesn´t use any index when runs the query. So,
we think this problem is bigger than the previous one.

We have sent to ftp://support.mysql.com/pub/mysql/secret the table definition
and data (albaranes.txt) in a compressed file named Albaranes.zip so you can
reproduce the bug.

We hope the test we are going to explain can help you to fix the bug.

Now, the table definition is (we have got 45055 records)

CREATE TABLE `albaranes` (
`Cli` varchar(6) NOT NULL default '',
`Alb` varchar(8) NOT NULL default '',
`Fac` varchar(8) NOT NULL default '',
`Ped` varchar(8) NOT NULL default '',
`Pis` char(2) NOT NULL default '',
`Fec` date default NULL,
`Dom` char(2) NOT NULL default '',
`FoP` char(2) NOT NULL default '',
`Ven` char(3) NOT NULL default '',
`Rep` char(3) NOT NULL default '',
`Ba1` decimal(20,4) NOT NULL default '0.0000',
`Ba2` decimal(20,4) NOT NULL default '0.0000',
`Ba3` decimal(20,4) NOT NULL default '0.0000',
`Ba4` decimal(20,4) NOT NULL default '0.0000',
`Iv1` float NOT NULL default '0',
`Iv2` float NOT NULL default '0',
`Iv3` float NOT NULL default '0',
`Iv4` float NOT NULL default '0',
`Re1` float NOT NULL default '0',
`Re2` float NOT NULL default '0',
`Re3` float NOT NULL default '0',
`Re4` float NOT NULL default '0',
`Dps` decimal(20,4) NOT NULL default '0.0000',
`Dpj` double NOT NULL default '0',
`Por` decimal(20,4) NOT NULL default '0.0000',
`TCV` decimal(20,4) NOT NULL default '0.0000',
`TCR` decimal(20,4) NOT NULL default '0.0000',
`TBe` decimal(20,4) NOT NULL default '0.0000',
`Nts` text,
`GAS` decimal(20,4) NOT NULL default '0.0000',
`ENV` varchar(40) NOT NULL default '',
`NFV` tinyint(4) unsigned NOT NULL default '0',
`NFR` tinyint(4) unsigned NOT NULL default '0',
`Usuario` char(2) NOT NULL default '',
`Tar` varchar(6) NOT NULL default '',
`Umv` date default NULL,
`Retenido` tinyint(4) unsigned NOT NULL default '0',
`Total` tinyint(4) unsigned NOT NULL default '0',
`Tipo_portes` tinyint(4) unsigned NOT NULL default '0',
`Agencia` char(2) NOT NULL default '',
`Nom_agencia` varchar(255) NOT NULL default '',
`Peso` float NOT NULL default '0',
`Volumen` float NOT NULL default '0',
`Num_expedicion` varchar(20) NOT NULL default '',
`Instalacion` varchar(20) NOT NULL default '',
`Bultos` smallint(6) NOT NULL default '0',
`Doc_manual` varchar(8) NOT NULL default '',
`Integrado` tinyint(4) unsigned NOT NULL default '0',
`Su_Pedido` varchar(40) NOT NULL default '',
`RecalculaPreciosCompra` tinyint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`Alb`),
UNIQUE KEY `Cliente` (`Cli`,`Alb`),
UNIQUE KEY `FecCliAlb` (`Cli`,`Fec`,`Alb`),
UNIQUE KEY `PorInstalacion` (`Cli`,`Instalacion`,`Fec`,`Alb`),
UNIQUE KEY `PorSuPedido` (`Cli`,`Su_Pedido`,`Fec`,`Alb`),
KEY `Facturas` (`Fac`)
) TYPE=InnoDB;

And now the tests:

INNODB
======
1) explain select * from albaranes where alb >'SA' limit 10
table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL,
ref=NULL,rows=45240,Extra=NULL.
Time: 0.85 secs.

MySql must use the PRIMARY index !!!

2) explain select alb from albaranes where alb >'SA' limit 10
table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY ,
key_len=8,ref=NULL,rows=58698,Extra=where used; Using index.
Time:0.05 secs.

If we only select the ALB field which belongs to the PRIMARY INDEX,
the query runs okey.

3) explain select * from albaranes where alb >'BA' limit 10
table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY ,
key_len=8,ref=null,rows=41440,Extra=where used.
Time:0.10 secs.

The records with alb >'BA' are at the beginning of the table. This
is the difference between explain 1 and explain 3.

4) explain select * from albaranes where alb >'MA' limit 10
table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL,
ref=NULL,rows=45240,Extra=where used.
Time: 0.80 secs.

MySql must use the PRIMARY index !!!

5) explain select * from albaranes where alb >'SB' limit 10
table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY ,
key_len=8,ref=null,rows=16224,Extra=where used.
Time:0.09 secs.

The records with alb>'SB' are at the end of the table.

6) explain select * from albaranes where alb < 'SB' limit 10
table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL,
ref=NULL,rows=44499,Extra=where used.
Time: 0.09 secs.

MySql must use the PRIMARY index !!! Although Mysql doesn´t use the
primary index, this query is as fast as the above one.

7) explain select alb from albaranes where alb >'WhatEver Value' limit 10
table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY ,
key_len=8,ref=NULL,rows=58698,Extra=where used; Using index.

If we only select the ALB field which belongs to the PRIMARY INDEX,
the query runs okey independtly of the 'Whatever Value'.


After that, we change the type table into MyIsam. If we run all the above queries,
the result is okey and the explain is as follow:

table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY ,
key_len=8,Extra=where used.

if we only select the field alb: Extra=where used; using index.

So we have no problems with MyIsam. MySql chooses the right index when
we use a table of type MyIsam.

Thanks in advance,
Rafa and Lourdes

How-To-Repeat:
selecting fields that don´t belong to the index used by mysql/Innodb

Fix:
-

Synopsis:optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb

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