List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 30 1999 6:11pm
Subject:Performance problem in mysql
View as plain text  
>>>>> "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
Thread
Performance problem in mysqlJoao Luis Silva Damas29 Jun
  • Re: Performance problem in mysqlBenjamin Pflugmann30 Jun
    • Re: Performance problem in mysqlJoao Luis Silva Damas30 Jun
      • Re: Performance problem in mysqlBenjamin Pflugmann1 Jul
  • Performance problem in mysqlMichael Widenius30 Jun
    • Re: Performance problem in mysqlJoao Luis Silva Damas1 Jul