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: [PHP-DB] 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...