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
[EMAIL PROTECTED]
______________________
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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
