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.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `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.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.', `TCR` decimal(20,4) NOT NULL default '0.', `TBe` decimal(20,4) NOT NULL default '0.', `Nts` text, `GAS` decimal(20,4) NOT NULL default '0.', `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
Re: optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb
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.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `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.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.', `TCR` decimal(20,4) NOT NULL default '0.', `TBe` decimal(20,4) NOT NULL default '0.', `Nts` text, `GAS` decimal(20,4) NOT NULL default '0.', `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
optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
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: tabletype possible_keys keykey_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: tabletype possible_keys keykey_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: 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] writes: cut rAnd 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 tabletype possible keys keykey_len refrowsExtra r - - ------ ---- r Clientes index PRIMARY,Nombre Nombre 464039where 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 [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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! rafarife == rafarife [EMAIL PROTECTED] writes: rafarife Description: rafarife Hi Heikki, rafarife I'm Lourdes and I work with Rafa in the MySQL project. rafarife We have done what you have told us. We have run SHOW STATUS before rafarife the query and we have obtained the next result: cut Handler_read_next = 0 cut rafarife Handler_read_next = 1929 The above shows that MySQL had to read 1929 rows to satsify the query. rafarife After doing this, we decided to create a new table like in your example with rafarife the same records. Then, we execute the query: cut rafarife The table definition is: rafarife CREATE TABLE `clientes` ( cut In cases like this that are data dependent, it's not enough for us to have the create table definition, we also need the data itself. rafarife How-To-Repeat: rafarifeSelect Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod'061642') rafarife Or Nom'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; To be able to find and fix the problem, we would need a copy the tables involved used by your query. Can you please do the following: - Make a mysqldump of your tables and make a README file of the above. - Tar and gzip or zip the files. - ftp the file to ftp://www.tcx.se/pub/mysql/secrent - Send an email to [EMAIL PROTECTED] that you have uploaded a test case. If you can do the above, we will try to fix the problem for next MySQL release. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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
Description: Heikki, 1) There is only one record with 'GARCIA MANCILLA,S.L'. 2) Mysql reported 53 scanned rows when I ran your mock-up query on your mock-up table. This is ok. 3) I'm using mysql.exe client (sometimes I use front-end) and i don´t think it removes the LIMIT 50 from my query. 4) I'm using mysqld-max-nt 4.0.3 beta. I have sent to pub/mysql/secret the table clientes (clientes.txt) and the my.ini file in the compressed file named clientes.zip. I tell you my test. a) 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 (You can verify it through the file clientes.txt I sent) b) I changed the table type to MyIsam, and I ran the above 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.00 secs. Handler_read_next: 49 So, I have no problems with your mock-up table because (I think) it has not too many fields. Also, in my real table, the query is only slow with Innodb table handler. It's fast with MyIsam table handler. I would like you can guess what the problem is and I thank you for your attention. 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: 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
