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