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