Re: sql optimizing assistance

run this query: EXPLAIN SELECT Question.Text_Long, AVG( Response ) FROM Response INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC and post the results. This will describe how MySQL is executing the query and can help you find a trouble spot. You may want to switch the order of the join. I know of LEFT JOINS it matters a lot, but I'm not sure about INNER's.. -Micah On Fri September 19 2003 12:41 pm, jeffrey_n_Dyke@Keane.com wrote: > I have two tables and am running a simple join between them to get > questions and their repsective response averages from a survey. The > question table has 49 rows and the Response table has 126,732. I'd like to > cut down on the time its taking to run this specific query...as i'll be > running many like it to generate reports. The query below is the selecting > the most data, normally this will be limited to specific groups by joining > more tables. > > I am executing the following query > SELECT Question.Text_Long, AVG( Response ) FROM Response INNER > JOIN > Question ON Question.Question_Key = Response.Question_Key WHERE > Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY > Question.Question_Key ASC > > Everything i've done so far leaves this query taking about 7-8 seconds to > excecute...and i'm trying to cut that time down. If i leave out the join > and just execute > -->SELECT Question_Key, AVG( Response ) FROM Response GROUP > BY > Question_Key > it takes about 3 seconds...is there anything i can do to speed the join up? > > i've tried using string functions instead of LIKE, but none of them proved > to be faster. i've also changed the table that i'm requesing the data from > and grouping by(Question and response)...all with mimimal impact. > > I'm running MySQL. 3.23 > > Thanks for any help/thoughts you may have. > have a good weekend. > Jeff > > > the table layout is > mysql> describe Response; > +--------------+------------+------+-----+---------+-------+ > > | Field | Type | Null | Key | Default | Extra | > > +--------------+------------+------+-----+---------+-------+ > > | Question_Key | int(11) | | PRI | 0 | | > | Survey_Key | int(11) | | PRI | 0 | | > | Response | tinyint(4) | | MUL | 0 | | > > +--------------+------------+------+-----+---------+-------+ > 3 rows in set (0.00 sec) > > mysql> describe 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) | YES | | NULL | | > | End_Date | datetime | YES | | NULL | | > | Question_Key | int(11) | | PRI | 0 | | > > +-----------------+--------------+------+-----+---------+-------+ > 7 rows in set (0.00 sec)