Rafa,
it looks like the tuning I did to 3.23.53 fixes the optimization also in the
'albaranes' case below.
Lenz starts building of 3.23.53 this week. With good luck 3.23.53 is
available around Oct 17, 2002.
Best regards,
Heikki
Innobase Oy
heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select count(*) from albaranes;
+----------+
| count(*) |
+----------+
| 46100 |
+----------+
1 row in set (0.59 sec)
mysql> explain select * from albaranes where alb >'SA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
1 row in set (0.00 sec)
mysql> explain select * from albaranes where alb >'BA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
1 row in set (0.00 sec)
mysql> explain select * from albaranes where alb >'MA' limit 10
-> ;
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
1 row in set (0.00 sec)
mysql> explain select * from albaranes where alb < 'SB' limit 10
-> ;
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra
|
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
| albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 |
where u
sed |
+-----------+-------+---------------+---------+---------+------+-------+----
----
----+
1 row in set (0.00 sec)
----- Original Message -----
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, October 09, 2002 1:17 PM
Subject: optimizer bug in selecting fields that donīt belong to the index
used by mysql/Innodb
> Description:
> Hello,
>
> We reported a problem about selecting fields that some of them didnīt
belong to
> the used index on September.
>
> We have already installed MySql 4.0.4 and we have a similar problem
> (and also we are still having the original problem) when we select
fields which some of them
> donīt belong to the primary index that MySql should use in the search.
In this query,
> we havenīt any OR condition in the WHERE clause as we had in the query
that we made
> on September. Moreover, in this case MySQL doesnīt use any index when
runs the query. So,
> we think this problem is bigger than the previous one.
>
> We have sent to ftp://support.mysql.com/pub/mysql/secret the table
definition
> and data (albaranes.txt) in a compressed file named Albaranes.zip so you
can
> reproduce the bug.
>
> We hope the test we are going to explain can help you to fix the bug.
>
> Now, the table definition is (we have got 45055 records)
>
> CREATE TABLE `albaranes` (
> `Cli` varchar(6) NOT NULL default '',
> `Alb` varchar(8) NOT NULL default '',
> `Fac` varchar(8) NOT NULL default '',
> `Ped` varchar(8) NOT NULL default '',
> `Pis` char(2) NOT NULL default '',
> `Fec` date default NULL,
> `Dom` char(2) NOT NULL default '',
> `FoP` char(2) NOT NULL default '',
> `Ven` char(3) NOT NULL default '',
> `Rep` char(3) NOT NULL default '',
> `Ba1` decimal(20,4) NOT NULL default '0.0000',
> `Ba2` decimal(20,4) NOT NULL default '0.0000',
> `Ba3` decimal(20,4) NOT NULL default '0.0000',
> `Ba4` decimal(20,4) NOT NULL default '0.0000',
> `Iv1` float NOT NULL default '0',
> `Iv2` float NOT NULL default '0',
> `Iv3` float NOT NULL default '0',
> `Iv4` float NOT NULL default '0',
> `Re1` float NOT NULL default '0',
> `Re2` float NOT NULL default '0',
> `Re3` float NOT NULL default '0',
> `Re4` float NOT NULL default '0',
> `Dps` decimal(20,4) NOT NULL default '0.0000',
> `Dpj` double NOT NULL default '0',
> `Por` decimal(20,4) NOT NULL default '0.0000',
> `TCV` decimal(20,4) NOT NULL default '0.0000',
> `TCR` decimal(20,4) NOT NULL default '0.0000',
> `TBe` decimal(20,4) NOT NULL default '0.0000',
> `Nts` text,
> `GAS` decimal(20,4) NOT NULL default '0.0000',
> `ENV` varchar(40) NOT NULL default '',
> `NFV` tinyint(4) unsigned NOT NULL default '0',
> `NFR` tinyint(4) unsigned NOT NULL default '0',
> `Usuario` char(2) NOT NULL default '',
> `Tar` varchar(6) NOT NULL default '',
> `Umv` date default NULL,
> `Retenido` tinyint(4) unsigned NOT NULL default '0',
> `Total` tinyint(4) unsigned NOT NULL default '0',
> `Tipo_portes` tinyint(4) unsigned NOT NULL default '0',
> `Agencia` char(2) NOT NULL default '',
> `Nom_agencia` varchar(255) NOT NULL default '',
> `Peso` float NOT NULL default '0',
> `Volumen` float NOT NULL default '0',
> `Num_expedicion` varchar(20) NOT NULL default '',
> `Instalacion` varchar(20) NOT NULL default '',
> `Bultos` smallint(6) NOT NULL default '0',
> `Doc_manual` varchar(8) NOT NULL default '',
> `Integrado` tinyint(4) unsigned NOT NULL default '0',
> `Su_Pedido` varchar(40) NOT NULL default '',
> `RecalculaPreciosCompra` tinyint(4) unsigned NOT NULL default '0',
> PRIMARY KEY (`Alb`),
> UNIQUE KEY `Cliente` (`Cli`,`Alb`),
> UNIQUE KEY `FecCliAlb` (`Cli`,`Fec`,`Alb`),
> UNIQUE KEY `PorInstalacion` (`Cli`,`Instalacion`,`Fec`,`Alb`),
> UNIQUE KEY `PorSuPedido` (`Cli`,`Su_Pedido`,`Fec`,`Alb`),
> KEY `Facturas` (`Fac`)
> ) TYPE=InnoDB;
>
> And now the tests:
>
> INNODB
> ======
> 1) explain select * from albaranes where alb >'SA' limit 10
> table=albaranes, type=ALL,
possible_keys=PRIMARY,key=NULL,Key_len=NULL,
> ref=NULL,rows=45240,Extra=NULL.
> Time: 0.85 secs.
>
> MySql must use the PRIMARY index !!!
>
> 2) explain select alb from albaranes where alb >'SA' limit 10
> table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY,
> key_len=8,ref=NULL,rows=58698,Extra=where used; Using index.
> Time:0.05 secs.
>
> If we only select the ALB field which belongs to the PRIMARY INDEX,
> the query runs okey.
>
> 3) explain select * from albaranes where alb >'BA' limit 10
> table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY,
> key_len=8,ref=null,rows=41440,Extra=where used.
> Time:0.10 secs.
>
> The records with alb >'BA' are at the beginning of the table. This
> is the difference between explain 1 and explain 3.
>
> 4) explain select * from albaranes where alb >'MA' limit 10
> table=albaranes, type=ALL,
possible_keys=PRIMARY,key=NULL,Key_len=NULL,
> ref=NULL,rows=45240,Extra=where used.
> Time: 0.80 secs.
>
> MySql must use the PRIMARY index !!!
>
> 5) explain select * from albaranes where alb >'SB' limit 10
> table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY,
> key_len=8,ref=null,rows=16224,Extra=where used.
> Time:0.09 secs.
>
> The records with alb>'SB' are at the end of the table.
>
> 6) explain select * from albaranes where alb < 'SB' limit 10
> table=albaranes, type=ALL,
possible_keys=PRIMARY,key=NULL,Key_len=NULL,
> ref=NULL,rows=44499,Extra=where used.
> Time: 0.09 secs.
>
> MySql must use the PRIMARY index !!! Although Mysql doesnīt use the
> primary index, this query is as fast as the above one.
>
> 7) explain select alb from albaranes where alb >'WhatEver Value' limit 10
> table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY,
> key_len=8,ref=NULL,rows=58698,Extra=where used; Using index.
>
> If we only select the ALB field which belongs to the PRIMARY INDEX,
> the query runs okey independtly of the 'Whatever Value'.
>
>
> After that, we change the type table into MyIsam. If we run all the above
queries,
> the result is okey and the explain is as follow:
>
> table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY,
> key_len=8,Extra=where used.
>
> if we only select the field alb: Extra=where used; using index.
>
> So we have no problems with MyIsam. MySql chooses the right index when
> we use a table of type MyIsam.
>
> Thanks in advance,
> Rafa and Lourdes
>
> How-To-Repeat:
> selecting fields that donīt belong to the index used by mysql/Innodb
>
> Fix:
> -
>
> Synopsis:optimizer bug in selecting fields that donīt belong to the index
used by mysql/innodb
>
> 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.4 beta(InnoDB)
>
> Exectutable: mysqld-max-nt
> Environment: Pentium III-MMX, 500 MHZ, 540 MB
> System: Windows 2000
> Compiler: -
> Architecture: i
---------------------------------------------------------------------
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
