I have four tables that i'm trying to join together most are pretty small(100-200 rows tops) and one, the Response table is 127,000 rows. The query i'm currently executing is
SELECT Business_Unit.Business_Unit, Question.Text_Long, AVG(Response) from Question INNER JOIN Response on Question.Question_Key = Response.Question_Key INNER JOIN Survey_Response on Survey_Response.Survey_Key = Response.Survey_Key INNER JOIN Business_Unit on Survey_Response.BUKey = Business_Unit.BUKey WHERE Question.SurveyID = 1 Group by Business_Unit.BUKey I'll actually need to join in two more tables, but to this point MySQL contol center won't execute the query, telling me that it would have to examine to many records. The Query above returns in about 12 seconds adn i'd really like to cut that down, if possible. When looking at EXPLAIN it doesn't seem to be using the first KEY from the Question table...which i'd have to imaging is slowing it down considerably..or is it, there seems to be very little information based on the rest of the data. +--------+--------+---------------+--------+---------+--------+--------+--------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+--------+---------------+--------+---------+--------+--------+--------+ | Question| ALL | PRIMARY | [NULL] | [NULL] | [NULL] | 49 | where used; Using temporary| | Response| ref | PRIMARY | PRIMARY| 4 | Question.Question_Key| 1267 | | | Survey_Response| eq_ref | PRIMARY | PRIMARY| 4 | Response.Survey_Key| 1 | | | Business_Unit| eq_ref | PRIMARY | PRIMARY| 4 | Survey_Response.BUKey| 1 | | +--------+--------+---------------+--------+---------+--------+--------+--------+ Any Thoughts/Suggestions are apprecitated. Jeff Question -- +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | [NULL] | | | Text_Short | varchar(255) | YES | | [NULL] | | | Category_ID | int(11) | YES | | [NULL] | | | SurveyID | int(11) | | PRI | 0 | | | End_Date | datetime | YES | | [NULL] | | | Question_Key | int(11) | | PRI | 0 | | +-----------------+--------------+------+-----+---------+-------+ **This has the Primary key at the end of the table...would this matter to MySQL??** The layout of the three tables are as follows Response (127,000) +--------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+---------+-------+ | Question_Key | int(11) | | PRI | 0 | | | Survey_Key | int(11) | | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--------------+------------+------+-----+---------+-------+ Survey_Response +-------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+----------------+ | Survey_Key | int(11) | | PRI | [NULL] | auto_increment | | Sex | varchar(5) | YES | | [NULL] | | | Age | varchar(5) | YES | | [NULL] | | | Ethnicity | varchar(5) | YES | | [NULL] | | | Title | varchar(5) | YES | | [NULL] | | | Functional_Area | varchar(5) | | | | | | Years_of_Service | varchar(5) | YES | | [NULL] | | | Employment_Source | varchar(20) | | | | | | BUKey | int(11) | YES | | [NULL] | | +-------------------+-------------+------+-----+---------+----------------+ Business_Unit +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | BUKey | int(11) | | PRI | 0 | | | BU_Number | int(11) | | | 0 | | | Business_Unit | varchar(55) | YES | | [NULL] | | | End_Date | datetime | YES | | [NULL] | | | RegionKey | int(11) | YES | | [NULL] | | | Count | int(6) | | | 0 | | +---------------+-------------+------+-----+---------+-------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
