MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Joao Luis Silva Damas Date:June 29 1999 2:13pm
Subject:Performance problem in mysql
View as plain text  
Hi,
we are using Mysql mysql-3.22.23b-sun-solaris2.6-sparc from the binary 
distribution on an Ultra 5 running Solaris2.6

We have come across a strange performance problem.

We have two tables:

TABLE 1
-------
Database: RIPE  Table: admin_c  Rows: 610656
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| object_id   | int(10)             |      | PRI | 0       |       |
| pe_ro_id    | int(10) unsigned    |      | PRI | 0       |       |
| object_type | tinyint(3) unsigned |      | MUL | 0       |       |
+-------------+---------------------+------+-----+---------+-------+
# Server version        3.22.23b
#
# Table structure for table 'admin_c'
#
CREATE TABLE admin_c (
  object_id int(10) DEFAULT '0' NOT NULL,
  pe_ro_id int(10) unsigned DEFAULT '0' NOT NULL,
  object_type tinyint(3) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (pe_ro_id,object_id),
  KEY object_type (object_type),
  KEY object_id (object_id)
);

and TABLE 2
    -------
#
# Table structure for table 'ID'
#
CREATE TABLE ID (
  id int(11)
);
This table has one row where id has a value of 961370 (just an integer).
mysql> select * from ID;
+--------+
| id     |
+--------+
| 961370 |
+--------+
1 row in set (0.12 sec)


On an unloaded machine (only the OS + mysqld running), issuing the following 
queries from the mysql tool gives:

mysql>  SELECT pe_ro_id  FROM ID, admin_c where admin_c.object_id = ID.id;
+----------+
| pe_ro_id |
+----------+
|   445745 |
+----------+
1 row in set (3.54 sec)

mysql> SELECT pe_ro_id  FROM admin_c where admin_c.object_id=961370;
+----------+
| pe_ro_id |
+----------+
|   445745 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ID, admin_c where admin_c.object_id = ID.id;
+--------+-----------+----------+-------------+
| id     | object_id | pe_ro_id | object_type |
+--------+-----------+----------+-------------+
| 961370 |    961370 |   445745 |           4 |
+--------+-----------+----------+-------------+
1 row in set (0.00 sec)

I can't understand why the first query takes at least 350 times longer than 
any of the other two.

Explain of the above queries says:
mysql> explain SELECT pe_ro_id  FROM ID, admin_c where admin_c.object_id = 
ID.id;
+---------+--------+---------------+---------+---------+------+--------+-------
--------+
| table   | type   | possible_keys | key     | key_len | ref  | rows   | Extra 
        |
+---------+--------+---------------+---------+---------+------+--------+-------
--------+
| ID      | system | NULL          | NULL    |    NULL | NULL |      1 |       
        |
| admin_c | index  | object_id     | PRIMARY |       8 | NULL | 610656 | ; 
Using index |
+---------+--------+---------------+---------+---------+------+--------+-------
--------+
2 rows in set (0.00 sec)

mysql> explain SELECT pe_ro_id  FROM admin_c where admin_c.object_id=961370;
+---------+------+---------------+-----------+---------+------+------+-------+
| table   | type | possible_keys | key       | key_len | ref  | rows | Extra |
+---------+------+---------------+-----------+---------+------+------+-------+
| admin_c | ref  | object_id     | object_id |       4 | ???  |    1 |       |
+---------+------+---------------+-----------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM ID, admin_c where admin_c.object_id = ID.id;
+---------+--------+---------------+-----------+---------+------+--------+-----
--+
| table   | type   | possible_keys | key       | key_len | ref  | rows   | 
Extra |
+---------+--------+---------------+-----------+---------+------+--------+-----
--+
| ID      | system | NULL          | NULL      |    NULL | NULL |      1 |     
  |
| admin_c | range  | object_id     | object_id |    NULL | NULL | 610656 |     
  |
+---------+--------+---------------+-----------+---------+------+--------+-----
--+
2 rows in set (0.00 sec)

Which definitely looks different for each query.

Any ideas?

Thanks,
Joao




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