>>>>> "Joao" == Joao Luis Silva Damas <joao@stripped> writes:
Joao> Hi,
Joao> we are using Mysql mysql-3.22.23b-sun-solaris2.6-sparc from the binary
Joao> distribution on an Ultra 5 running Solaris2.6
Joao> We have come across a strange performance problem.
Joao> We have two tables:
Joao> TABLE 1
Joao> -------
Joao> Database: RIPE Table: admin_c Rows: 610656
Joao> +-------------+---------------------+------+-----+---------+-------+
Joao> | Field | Type | Null | Key | Default | Extra |
Joao> +-------------+---------------------+------+-----+---------+-------+
Joao> | object_id | int(10) | | PRI | 0 | |
Joao> | pe_ro_id | int(10) unsigned | | PRI | 0 | |
Joao> | object_type | tinyint(3) unsigned | | MUL | 0 | |
Joao> +-------------+---------------------+------+-----+---------+-------+
Joao> # Server version 3.22.23b
Joao> #
Joao> # Table structure for table 'admin_c'
Joao> #
Joao> CREATE TABLE admin_c (
Joao> object_id int(10) DEFAULT '0' NOT NULL,
Joao> pe_ro_id int(10) unsigned DEFAULT '0' NOT NULL,
Joao> object_type tinyint(3) unsigned DEFAULT '0' NOT NULL,
Joao> PRIMARY KEY (pe_ro_id,object_id),
Joao> KEY object_type (object_type),
Joao> KEY object_id (object_id)
Joao> );
Joao> and TABLE 2
Joao> -------
Joao> #
Joao> # Table structure for table 'ID'
Joao> #
Joao> CREATE TABLE ID (
Joao> id int(11)
Joao> );
Joao> This table has one row where id has a value of 961370 (just an integer).
mysql> select * from ID;
Joao> +--------+
Joao> | id |
Joao> +--------+
Joao> | 961370 |
Joao> +--------+
Joao> 1 row in set (0.12 sec)
Joao> On an unloaded machine (only the OS + mysqld running), issuing the following
Joao> queries from the mysql tool gives:
mysql> SELECT pe_ro_id FROM ID, admin_c where admin_c.object_id = ID.id;
Joao> +----------+
Joao> | pe_ro_id |
Joao> +----------+
Joao> | 445745 |
Joao> +----------+
Joao> 1 row in set (3.54 sec)
mysql> SELECT pe_ro_id FROM admin_c where admin_c.object_id=961370;
Joao> +----------+
Joao> | pe_ro_id |
Joao> +----------+
Joao> | 445745 |
Joao> +----------+
Joao> 1 row in set (0.00 sec)
<cut>
Hi!
This was a bug in the optimizer; It first resolves all const tables
to constants, but it then estimated wrong the number of rows that
would be matched by the index.
(isamchk -a admin_c, would have fixed this)
Here is a patch for MySQL 3.22.23b that fixes this:
*** /my/monty/master/mysql-3.22.23b/sql/sql_select.cc Sat May 15 00:40:06 1999
--- ./sql_select.cc Wed Jun 30 21:09:59 1999
***************
*** 1345,1354 ****
** part of this table.
** For example 100 records matching this table with 5000 records
** gives 5000/100 = 50 records per key
*/
! if (keyuse->field &&
rec > keyuse->field->table->keyfile_info.records)
! rec=keyuse->field->table->keyfile_info.records;
keyuse++;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
--- 1345,1357 ----
** part of this table.
** For example 100 records matching this table with 5000 records
** gives 5000/100 = 50 records per key
+ ** Constant tables are ignored and to avoid bad matches,
+ ** we don't make rec less than 100.
*/
! if (keyuse->field && ! (keyuse->field->table->map &
! join->const_bits) &&
rec > keyuse->field->table->keyfile_info.records)
! rec=max(keyuse->field->table->keyfile_info.records,100);
keyuse++;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
Regards,
Monty
