Rafa, I fixed the optimization of the ORDER BY ... LIMIT 50 query on the generated 'clientes' table you sent in September. The fix is in 3.23.53. It will favor index range scans even more over full index scans.
But the underlying problem is in the MySQL optimizer. It does not realize that it can use the LIMIT to restrict the number of rows to be fetched. That may be fixed in 4.1 at the earliest. I will take a look at the optimizer problem below. Thank you, Heikki Innobase Oy ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, October 09, 2002 12:56 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 __________________________________________________________________ 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 [EMAIL PROTECTED] To unsubscribe, e-mail <[EMAIL PROTECTED]> --------------------------------------------------------------------- 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
