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

Description:
Heikki,

I am sorry because I have made a mistake. I have not two keys with
the same name.

The Clientes table is as follow:

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 NombreComercial (Nombre_comercial,Cod) !!!!
...
...
) TYPE=InnoDB;

Mysql uses the index: Nombre

And now, the explain selects:


1) Slow query
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

2) Fast queries.

Temporary table.
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;

explain 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;

table type possible keys key key_len ref rows Extra
----- ---- ------------- --- ------- --- ---- -----
Clientes range PRIMARY,Nombre Nombre 46 4411 where used;using index


explain SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL
FROM TEMPORAL INNER JOIN CLIENTES ON TEMPORAL.COD=CLIENTES.COD;

table type possible keys key key_len ref rows Extra
----- ---- ------------- --- ------- --- ---- -----
Temporal all 50
Clientes eq_ref PRIMARY PRIMARY 6 Temporal.cod 1



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

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

Hi!

>>>>> "r" == rafarife writes:




r> And now, the explain selects:

r> 1) Slow query
r> explain Select Cod,Nom,Nif,Nombre_comercial from Clientes
r> Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642')
r> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;

r> table type possible keys key key_len ref rows Extra
r> ----- ---- ------------- --- ------- --- ---- -----
r> Clientes index PRIMARY,Nombre Nombre 46 4039 where used

The above explains what happens. For some strange reason MySQL uses a
full index scan above instead of a 'range' query.

This really looks like a bug in the optimiser.

To solve this we would however need a copy of your tables to be able
to repeat this.

Can you please ftp them to ftp://support.mysql.com/pub/mysql/secret so
that we can try to fix ASAP ?

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.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

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

On Friday 13 September 2002 20:56, rafarife@netscape.net wrote:
> Description:
> Heikki,
>
> I am sorry because I have made a mistake. I have not two keys with
> the same name.
>
>
>
> 1) Slow query
> explain Select Cod,Nom,Nif,Nombre_comercial from Clientes
> Where (Nom =3D '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
>


What is the speed difference between this and next "fast" query ?

This query should be slower compared to the query which uses only column =
covered by index
because in this case innodb not only has to scan index until it finds goo=
d enough number of
rows but additionally preform the read on clustered index as well. This m=
ay be much slower or may be
just slightly slower depending on if this data is presents in innodb buff=
er pool.




--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


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