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

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

am 23.09.2002 09:52:49 von rafarife

Description:
Hello,

I wrote the following text in my last message (It was an answer to Monty):

>Hello,
> Monty
> I have already sent to pub/mysql/secret the table definition and data (clientes.txt)
> and my.ini file in a compressed file named clientes.zip.

> You can import clientes.txt and test it.

> I have the problem only with InnoDb tables.

> I tell you my test with the above table clientes (


> InnoDB table
> ============
> I ran the following query:

> Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And
> Cod>'059336') Or Nom>'a2618' Order by Nom ASC, Cod ASC Limit 50;

> Time:0.08 secs.
> Handler_read_next: 1850

> Explain:
> table type possible_keys key key_len ref rows Extra
> Clientes index PRIMARY,Nombre Nombre 46 NULL 3899 where used


> I changed the table type to MyIsam (alter table clientes type=MyIsam),
> and I ran the above query:

> MyIsam table
> ============
> Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And
> Cod>'059336') Or Nom>'a2618' Order by Nom ASC, Cod ASC Limit 50;

> Time:0.00 secs.
> Handler_read_next: 49

> Explain:
> table type possible_keys key key_len ref rows Extra
> Clientes range PRIMARY,Nombre Nombre 46 NULL 4057 where used

> Thanks in advance,
> Rafa

and I have received no answer, so, Have you received the file clientes.zip?
Have you tested it?
Let me know about it.

Thanks in advance,
Rafa

How-To-Repeat:
Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And
Cod>'059336') Or Nom>'a2618' 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

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

am 25.09.2002 14:36:43 von Michael Widenius

Hi!

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

rafarife> Description:
rafarife> Hello,

rafarife> I wrote the following text in my last message (It was an answer to Monty):

>> Hello,
>> Monty
>> I have already sent to pub/mysql/secret the table definition and data (clientes.txt)
>> and my.ini file in a compressed file named clientes.zip.



rafarife> and I have received no answer, so, Have you received the file clientes.zip?
rafarife> Have you tested it?
rafarife> Let me know about it.



rafarife> How-To-Repeat:
rafarife> Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And
Cod> '059336') Or Nom>'a2618' Order by Nom ASC, Cod ASC Limit 50;

I just wanted to note that I have this on my TODO and will do this
ASAP; We must however first get 4.0.4 and 4.1 on track before I will
have time to look at this problem (as this is not a critical bug).

My estimate is that I will be able to look at this within about 14 days.

Regards,
Monty


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