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.',
`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.',
