Jeff, Try creating a new index on Question containing just the question_key field, and try it again.
Andy > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 22 September 2003 13:23 > To: [EMAIL PROTECTED] > Subject: join not using first primay key, per explain > > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
