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