Unfortunately didn't that help, it leads to:
+----+-------------+-------+-------+-------
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+-------+-------+-------
| 1 | SIMPLE | ps | range |
phrase_search,id_search,phrase_date | id_search | 3 | NULL
| 3836930 | Using where; Using temporary; Using filesort
Yeh it's finding a lot more rows there which isn't what you want so the
extra time isn't surprising.
Does rewriting the query to be an inner join help?
EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw INNER JOIN phrase_searches ps ON
(ps.phrase_id=pw.phrase_id) WHERE
pw.word_id IN (966,1,1250,1741) AND ps.search_date >=
'2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;
or even:
EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM
phrase_words pw, phrase_searches ps WHERE
pw.phrase_id = ps.phrase_id AND pw.word_id IN (966,1,1250,1741) AND
ps.search_date >= '2006-07-17' AND ps.search_date <= '2006-08-16'
GROUP by pw.word_id;
(which puts the join between the two tables first).
That would help with this discussion too:
http://lists.mysql.com/mysql/201015
;)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
