ahhh yes, thanks for pointing that out. it was not origianlly designed as such and then the codes changed. time is about the same though
Thanks gerald_clark <[EMAIL PROTECTED] To: [EMAIL PROTECTED] ystems.com> cc: [EMAIL PROTECTED] Subject: Re: join optimization 09/19/2003 04:05 PM [EMAIL PROTECTED] 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 > You are doing a string compare on an integer field. Why? > >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) > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]