optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
am 23.09.2002 09:52:49 von rafarifeDescription:
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