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: <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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
