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

Description:
Heikki,

I dropped the or condition but the query runs as slow as it ran before.

. With the or condition.
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

Time of execution: 0.12 secs.

.. Without the or condition
explain Select Cod,Nom,Nif,Nombre_comercial from Clientes
Where 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 Nombre Nombre 46 4039 where used

Time of execution: 0.11 secs.


With the temporary table.
1) 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
Time of execution in the creation of the temporary table: 0.01 secs.
2) SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL
FROM TEMPORAL INNER JOIN CLIENTES ON TEMPORAL.COD=CLIENTES.COD;
Time of execution in joining the temporary table with the table clientes: 0.03 secs.
3) Total time: 0.04 seg.

In a continous execution of the query, 0.12 secs. vs 0.04 secs is too much time.
And I need the or condition !!!

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