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) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
