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]
