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

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


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