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

Description:
Hello,

I am working with mysqld-max-nt under windows 2000 and unsing InnoDb
tables.

I have the following table:

CREATE TABLE clientes (
Cod varchar(6) NOT NULL default '',
Nom varchar(40) NOT NULL default '',
Nif varchar(10) NOT NULL default '',
Nombre_comercial varchar(40) NOT NULL default '',
...
...
...
PRIMARY KEY (Cod),
UNIQUE KEY Nombre (Nom,Cod)
UNIQUE KEY Nombre (Nombre_comercial,Cod)
...
...
) TYPE=InnoDB;

I don´t understand why this select:

Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642')
Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
(Mysql uses the unique index: Nombre)

is much slower than the following selects in which I use a temporary table:

DROP TABLE IF EXISTS TEMPORAL;

CREATE TEMPORARY TABLE TEMPORAL type=heap Select Cod,Nom From Clientes
Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
(I select Cod,Nom wich are fields from the unique index Nombre, so it's very fast).

(I select the other fields (nif,nombre_comercial) which don´t belong to the unique index named Nombre).
SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL
FROM TEMPORAL INNER JOIN CLIENTES ON TEMPORAL.COD=CLIENTES.COD;
(Inner join through the field cod, which is the primary index, so it's very fast).

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

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

Re: optimizer bug in selecting fields that don´t belon

Rafa,

OR's are generally difficult to optimize.

The reason for the slowness of the first query might be that MySQL actually
scans a large part of the unique key Nombre (actually, why do you have two
keys with the same name? do you know which key MySQL is using?).

An ORDER BY causes InnoDB to fetch also the clustered index records for each
of these Nombre records. That takes time.

Could you rename your keys and print EXPLAIN SELECT for the slow query and
the fast query so that we see if this is actually the case?

Thank you,

Heikki
Innobase Oy


Copied message:
.............
Subject:Subject: optimizer bug in selecting fields that don´t belong to the
index used by mysql/innodb
From: rafarife.netscape.net
Date: Fri, 13 Sep 2002 10:20:30 -0400



Description:
Hello,

I am working with mysqld-max-nt under windows 2000 and unsing InnoDb
tables.

I have the following table:

CREATE TABLE clientes (
Cod varchar(6) NOT NULL default '',
Nom varchar(40) NOT NULL default '',
Nif varchar(10) NOT NULL default '',
Nombre_comercial varchar(40) NOT NULL default '',
...
...
...
PRIMARY KEY (Cod),
UNIQUE KEY Nombre (Nom,Cod)
UNIQUE KEY Nombre (Nombre_comercial,Cod)
...
...
) TYPE=InnoDB;

I don´t understand why this select:

Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA
MANCILLA,S.L'
And Cod>'061642')
Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
(Mysql uses the unique index: Nombre)

is much slower than the following selects in which I use a temporary
table:

DROP TABLE IF EXISTS TEMPORAL;

CREATE TEMPORARY TABLE TEMPORAL type=heap Select Cod,Nom From Clientes
Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') Or Nom>'GARCIA
MANCILLA,S.L'
Order by Nom ASC, Cod ASC Limit 50;
(I select Cod,Nom wich are fields from the unique index Nombre, so it's
very
fast).

(I select the other fields (nif,nombre_comercial) which don´t belong to
the
unique index named Nombre).
SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL
FROM TEMPORAL INNER JOIN CLIENTES ON TEMPORAL.COD=CLIENTES.COD;
(Inner join through the field cod, which is the primary index, so it's
very
fast).


I have about 4000 records in the table.

thanks in advance,
Rafa.

How-To-Repeat:
Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA
MANCILLA,S.L'
And Cod>'061642')
Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;

Fix:
-

Synopsis: Subject: 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.3 beta(InnoDB)

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





------------------------------------------------------------ ---------
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 selecting fields that don´t belon

Rafa,

----- Original Message -----
From:
Newsgroups: mailing.database.mysql
Sent: Friday, September 13, 2002 7:59 PM
Subject: optimizer bug in selecting fields that don´t belong to the index
used by mysql/innodb


> Description:
> Heikki,
>
> I am sorry because I have made a mistake. I have not two keys with
> the same name.
>
> The Clientes table is as follow:
>
> CREATE TABLE clientes (
> Cod varchar(6) NOT NULL default '',
> Nom varchar(40) NOT NULL default '',
> Nif varchar(10) NOT NULL default '',
> Nombre_comercial varchar(40) NOT NULL default '',
> ...
> ...
> ...
> PRIMARY KEY (Cod),
> UNIQUE KEY Nombre (Nom,Cod)
> UNIQUE KEY NombreComercial (Nombre_comercial,Cod) !!!!
> ...
> ...
> ) TYPE=InnoDB;
>
> Mysql uses the index: Nombre
>
> And now, the explain selects:
>
>
> 1) Slow query
> explain Select Cod,Nom,Nif,Nombre_comercial from Clientes
> Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642')
> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
>
> table type possible keys key key_len ref rows Extra
> ----- ---- ------------- --- ------- --- ---- -----
> Clientes index PRIMARY,Nombre Nombre 46 4039 where used


looks like it may scan all the 4000 index records and retrieve each
clustered index record.


> 2) Fast queries.
>
> Temporary table.
> CREATE TEMPORARY TABLE TEMPORAL type=heap Select Cod,Nom From Clientes
> Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') Or
Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
>
> explain Select Cod,Nom From Clientes Where (Nom = 'GARCIA MANCILLA,S.L'
And Cod>'061642')
> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
>
> table type possible keys key key_len ref rows Extra
> ----- ---- ------------- --- ------- --- ---- -----
> Clientes range PRIMARY,Nombre Nombre 46 4411 where
used;using index


In the scan we do not need to fetch clustered index records.


> explain SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL
> FROM TEMPORAL INNER JOIN CLIENTES ON
TEMPORAL.COD=CLIENTES.COD;
>
> table type possible keys key key_len ref rows Extra
> ----- ---- ------------- --- ------- --- ---- -----
> Temporal all 50
> Clientes eq_ref PRIMARY PRIMARY 6 Temporal.cod 1


Only 50 clustered index records need to be fetched.

You can play by taking the OR apart. Does the query run fast if you drop
either of the OR conditions?

> thanks in advance,
> Rafa.

Thank you,

Heikki



> How-To-Repeat:
> Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA
MANCILLA,S.L' And Cod>'061642')
> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
>
> Fix:
> -
>
> Synopsis: Subject: 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.3 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
>



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

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

Hi!

>>>>> "rafarife" == rafarife writes:

rafarife> Description:
rafarife> Hello,

rafarife> I am working with mysqld-max-nt under windows 2000 and u=
nsing InnoDb
rafarife> tables.

rafarife> I have the following table: =20



rafarife> I don=B4t understand why this select:
=20
rafarife> Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (N=
om =3D 'GARCIA MANCILLA,S.L' And Cod>'061642')=20
rafarife> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Li=
mit 50;
rafarife> (Mysql uses the unique index: Nombre)

What is the output from EXPLAIN for this query ?
Do you get a similar problem with MyISAM tables ?

Regards,
Monty

------------------------------------------------------------ ---------
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 selecting fields that don´t belon

Hi!

>>>>> "Heikki" == Heikki Tuuri writes:

Heikki> Rafa,
Heikki> OR's are generally difficult to optimize.

Heikki, this is true if you are using OR on different keys.
MySQL can fully optimise the query when using OR on different key parts of the
same key (which is the case for the used query:)

Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;

In this case MySQL should start scanning the table from the key

[(GARCIA MANCILLA),(061642)[

and continue until it has found 50 rows.

Regards,
Monty

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

InnoDB 3.23.52, foreign keys and update/cascade problem

Hi all:

I'm seeing a strange problem updating a field if
that field is referenced as a FK in another table.

Consider:

CREATE TABLE A (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id)
);

CREATE TABLE B (
id INTEGER NOT NULL AUTO_INCREMENT,
AID INTEGER,
name VARCHAR(20),
PRIMARY KEY (id),
INDEX(AID) ,
FOREIGN KEY (AID) REFERENCES A (id)
ON UPDATE CASCADE
);


mysql> insert into A values
(null, 'one'), (null, 'two');

mysql> select * from A;
+----+------+
| id | name |
+----+------+
| 1 | one |
| 2 | two |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into B values
(null, 1, 'hello'), (null, 1, 'world');


mysql> select * from B;
+----+------+-------+
| id | AID | name |
+----+------+-------+
| 1 | 1 | hello |
| 2 | 1 | world |
+----+------+-------+
2 rows in set (0.00 sec)

AND HERE IS THE PROBLEM:

mysql> update A set id = 5 where id = 1;
ERROR 1217: Cannot delete a parent row: a foreign key
constraint fails

---- Note, the innodb manual says: ----
If you defined ON DELETE CASCADE or SET NULL and
updated the referenced key in the parent row, InnoDB
deleted or updated the child row. This is now changed
to conform to SQL-92: you get the error 'Cannot delete
parent row'.
---------------------------------------------------

NOTE, There is no 'ON DELETE' constraint in my example
so what on earth is going on ?

Can anyone explain why an update is not getting
cascaded ? I mean, that's the whole POINT of the
update constraint. What am I doing wrong ?

Best regards,

--j


__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.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: InnoDB 3.23.52, foreign keys and update/cascade problem

Hi all:

I'm seeing a strange problem updating a field if
that field is referenced as a FK in another table.

Consider:

CREATE TABLE A (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id)
);

CREATE TABLE B (
id INTEGER NOT NULL AUTO_INCREMENT,
AID INTEGER,
name VARCHAR(20),
PRIMARY KEY (id),
INDEX(AID) ,
FOREIGN KEY (AID) REFERENCES A (id)
ON UPDATE CASCADE
);


mysql> insert into A values
(null, 'one'), (null, 'two');

mysql> select * from A;
+----+------+
| id | name |
+----+------+
| 1 | one |
| 2 | two |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into B values
(null, 1, 'hello'), (null, 1, 'world');


mysql> select * from B;
+----+------+-------+
| id | AID | name |
+----+------+-------+
| 1 | 1 | hello |
| 2 | 1 | world |
+----+------+-------+
2 rows in set (0.00 sec)

AND HERE IS THE PROBLEM:

mysql> update A set id = 5 where id = 1;
ERROR 1217: Cannot delete a parent row: a foreign key
constraint fails

---- Note, the innodb manual says: ----
If you defined ON DELETE CASCADE or SET NULL and
updated the referenced key in the parent row, InnoDB
deleted or updated the child row. This is now changed
to conform to SQL-92: you get the error 'Cannot delete
parent row'.
---------------------------------------------------

NOTE, There is no 'ON DELETE' constraint in my example
so what on earth is going on ?

Can anyone explain why an update is not getting
cascaded ? I mean, that's the whole POINT of the
update constraint. What am I doing wrong ?

Best regards,

--j


__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.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: InnoDB 3.23.52, foreign keys and update/cascade problem

At 12:10 -0700 9/23/02, j.random.programmer wrote:
>Hi all:
>
>I'm seeing a strange problem updating a field if
>that field is referenced as a FK in another table.
>
>Consider:
>
>CREATE TABLE A (
> id INTEGER NOT NULL AUTO_INCREMENT,
> name VARCHAR(20),
> PRIMARY KEY (id)
>);
>
>CREATE TABLE B (
> id INTEGER NOT NULL AUTO_INCREMENT,
> AID INTEGER,
> name VARCHAR(20),
> PRIMARY KEY (id),
> INDEX(AID) ,
> FOREIGN KEY (AID) REFERENCES A (id)
> ON UPDATE CASCADE
>);

I believe that ON UPDATE CASCADE doesn't work yet.

>
>
>mysql> insert into A values
>(null, 'one'), (null, 'two');
>
>mysql> select * from A;
>+----+------+
>| id | name |
>+----+------+
>| 1 | one |
>| 2 | two |
>+----+------+
>2 rows in set (0.00 sec)
>
>mysql> insert into B values
>(null, 1, 'hello'), (null, 1, 'world');
>
>
>mysql> select * from B;
>+----+------+-------+
>| id | AID | name |
>+----+------+-------+
>| 1 | 1 | hello |
>| 2 | 1 | world |
>+----+------+-------+
>2 rows in set (0.00 sec)
>
>AND HERE IS THE PROBLEM:
>
>mysql> update A set id = 5 where id = 1;
>ERROR 1217: Cannot delete a parent row: a foreign key
>constraint fails
>
>---- Note, the innodb manual says: ----
>If you defined ON DELETE CASCADE or SET NULL and
>updated the referenced key in the parent row, InnoDB
>deleted or updated the child row. This is now changed
>to conform to SQL-92: you get the error 'Cannot delete
>parent row'.
>---------------------------------------------------
>
>NOTE, There is no 'ON DELETE' constraint in my example
>so what on earth is going on ?
>
>Can anyone explain why an update is not getting
>cascaded ? I mean, that's the whole POINT of the
>update constraint. What am I doing wrong ?
>
>Best regards,
>
>--j


------------------------------------------------------------ ---------
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 selecting fields that don´t belong

Rafa,

I fixed the optimization of the ORDER BY ... LIMIT 50 query on the generated
'clientes' table you sent in September. The fix is in 3.23.53. It will favor
index range scans even more over full index scans.

But the underlying problem is in the MySQL optimizer. It does not realize
that it can use the LIMIT to restrict the number of rows to be fetched. That
may be fixed in 4.1 at the earliest.

I will take a look at the optimizer problem below.

Thank you,

Heikki

Innobase Oy

----- Original Message -----
From:
To: ;
Sent: Wednesday, October 09, 2002 12:56 PM
Subject: optimizer bug in selecting fields that don´t belong to the index
used by mysql/Innodb


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





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

Re: optimizer bug in selecting fields that don´t belon

Rafa,

it looks like the tuning I did to 3.23.53 fixes the optimization also in the
'albaranes' case below.

Lenz starts building of 3.23.53 this week. With good luck 3.23.53 is
available around Oct 17, 2002.

Best regards,

Heikki
Innobase Oy


heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from albaranes;
+----------+
| count(*) |
+----------+
| 46100 |
+----------+
1 row in set (0.59 sec)

mysql> explain select * from albaranes where alb >'SA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
1 row in set (0.00 sec)

mysql> explain select * from albaranes where alb >'BA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
1 row in set (0.00 sec)

mysql> explain select * from albaranes where alb >'MA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
1 row in set (0.00 sec)

mysql> explain select * from albaranes where alb < 'SB' limit 10
-> ;
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+--- ---+-------+----
----
----+
1 row in set (0.00 sec)

----- Original Message -----
From:
Newsgroups: mailing.database.mysql
Sent: Wednesday, October 09, 2002 1:17 PM
Subject: optimizer bug in selecting fields that don´t belong to the index
used by mysql/Innodb


> 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




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