List:General Discussion« Previous MessageNext Message »
From:Anand Vaddiraju Date:December 7 2001 1:05am
Subject:Query optimization (Using temporary; Using filesort)
View as plain text  
Hello,

No matter how hard I try, I am not able to make this query work
without using a temp table (which, as I understand from the documentation,
is not a very good thing).  Here are the various details, and if anyone
can give me any pointers on optimzing this query, I will really appreciate
it.  I am still in the development stage, and so don't mind changing the table
structure if required.

Thanks in advance,
Anand



EXPLAIN
SELECT Profile.Person.name,
       Design_Issue.id,
       Design_Issue.issue,
       Design_Issue.chip_type_id,
       Design_Issue.chip_section_id,
       Design_Issue.created,
       Status_Type.pstatus,
       Problem_Type.ptype

FROM   Profile.Person,
       Design_Issue,
       Status_Type,
       Problem_Type

WHERE  Profile.Person.id         = Design_Issue.author_id AND
       Status_Type.id            = Design_Issue.status_type_id AND
       Problem_Type.id           = Design_Issue.problem_type_id

ORDER BY Profile.Person.name;

+--------------+--------+----------------+----------------+---------+------------------------------+------+---------------------------------+
| table        | type   | possible_keys  | key            | key_len | ref                 
        | rows | Extra                           |
+--------------+--------+----------------+----------------+---------+------------------------------+------+---------------------------------+
| Status_Type  | ALL    | PRIMARY        | NULL           |    NULL | NULL                
        |   13 | Using temporary; Using filesort |
| Design_Issue | ref    | status_type_id | status_type_id |       3 | Status_Type.id      
        |  102 |                                 |
| Person       | eq_ref | PRIMARY        | PRIMARY        |       4 |
Design_Issue.author_id       |    1 |                                 |
| Problem_Type | eq_ref | PRIMARY        | PRIMARY        |       1 |
Design_Issue.problem_type_id |    1 | where used                      |
+--------------+--------+----------------+----------------+---------+------------------------------+------+---------------------------------+


mysql> explain Profile.Person;
+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| id      | int(10) unsigned    |      | PRI | 0       |       |
| name    | varchar(100)        |      |     |         |       |
| company | varchar(100)        |      |     |         |       |
| email   | varchar(125)        |      |     |         |       |
| phone   | varchar(25)         |      |     |         |       |
| ugroup  | tinyint(3) unsigned |      |     | 0       |       |
| other   | tinytext            | YES  |     | NULL    |       |
+---------+---------------------+------+-----+---------+-------+


mysql> explain Design_Issue;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| id              | int(10) unsigned      |      | PRI | NULL    | auto_increment |
| author_id       | int(10) unsigned      |      |     | 0       |                |
| problem_type_id | mediumint(8) unsigned |      |     | 0       |                |
| chip_type_id    | mediumtext            |      |     |         |                |
| chip_section_id | mediumtext            |      |     |         |                |
| audience_id     | mediumint(8) unsigned |      |     | 0       |                |
| status_type_id  | mediumint(8) unsigned |      | MUL | 0       |                |
| problem_body_id | int(10) unsigned      |      |     | 0       |                |
| created         | timestamp(8)          | YES  |     | NULL    |                |
| last_modified   | timestamp(14)         | YES  |     | NULL    |                |
| issue           | tinytext              |      |     |         |                |
+-----------------+-----------------------+------+-----+---------+----------------+


+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id      | mediumint(8) unsigned |      | PRI | NULL    | auto_increment |
| pstatus | varchar(50)           |      | UNI |         |                |
+---------+-----------------------+------+-----+---------+----------------+


mysql> explain Problem_Type;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| ptype | varchar(25)         |      | UNI |         |                |
+-------+---------------------+------+-----+---------+----------------+



______________________
Anand S. Vaddiraju
anand@stripped
______________________
Economists state their GNP growth projections to the nearest tenth of a
percentage point to prove they have a sense of humor.
		-- Edgar R. Fiedler

Thread
Query optimization (Using temporary; Using filesort)Anand Vaddiraju7 Dec
  • Re: Query optimization (Using temporary; Using filesort)Harald Fuchs7 Dec