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
