Thanks Jeff!
You were correct the Question_Key was set second in the Primary key...i
changed that and the query went down to 2.41seconds. very nice.
Thanks for the help.
Jeff
ps. you're also correct about the LIKE, of course...that was being used
horribly. i'd forgoten it changed to an INT, and i was using a string
function on it. it is now using that appropritately as well.
Jeff Shapiro
<lists@nensha.com To: jeffrey_n_Dyke@Keane.com
> cc: php-db@lists.php.net
Subject: Re: sql optimizing assistance
09/19/2003 05:42
PM
What is the exact order of columns in your PRIMARY KEY in the Response
table? If Question_Key is not the first column listed in the key, then
MySQL won't use it.
Try the following:
SELECT Question.Text_Long, AVG( Response )
FROM `Response` USE INDEX (Question_key)
JOIN Question ON Response.Question_Key = Question.Question_Key
WHERE Question.Question_Key LIKE '2003%'
GROUP BY Response.Question_Key
ORDER BY Question.Question_Key ASC
This should force MySQL to use your index.
According to the MySQL manual the LIKE function does use indexes when
you use it the way that you are using it. For more info:
http://www.mysql.com/doc/en/MySQL_indexes.html
On Fri, 19 Sep 2003 16:01:34 -0400, jeffrey_n_Dyke@Keane.com spoke
thusly about Re: sql optimizing assistance:
>
> i new i forgot to include something.
>
> here this is the ouput. it looks like its using none of my indexes in
the
> Response, which is where i would think it would need it the most.
>
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>> table | type | possible_keys | key | key_len | ref | rows |
> Extra |
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>> Response| ALL | [NULL] | [NULL] | [NULL] | [NULL] | 126732 |
> Using temporary; Using filesort|
>> Question| eq_ref | PRIMARY | PRIMARY| 4 |
> Response.Question_Key| 1 | where used|
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>
> even on the second query where there is no join, its not using the keys.
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>> table | type | possible_keys | key | key_len | ref | rows |
> Extra |
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>> Response| ALL | [NULL] | [NULL] | [NULL] | [NULL] | 126732 |
> Using temporary|
>
+--------+--------+---------------+--------+---------+--------+--------+--------+
>
> Thanks,
> Jeff
>
>
>
> "CPT John
>
W.
> Holmes" To:
> <php-db@lists.php.net>,
> <jeffrey_n_Dyke@Keane.com>
> <holmes072000@cha
>
cc:
> rter.net> Subject: Re:
> sql optimizing assistance
>
> 09/19/2003
>
03:59
>
>
PM
> Please respond
>
to
> "CPT John
>
W.
>
>
Holmes"
>
>
>
>
>
>
> From: <jeffrey_n_Dyke@Keane.com>
>
>
>> 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
>
> What does EXPLAIN tell you for this query? Is it using your indexes?
>
> ---John Holmes...
---
Listserv only address.
Jeff Shapiro
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
