On 8/17/06, Chris <[EMAIL PROTECTED]> wrote:
> 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 didn't help either. Same amount of rows as my first join and about the same speed as well (only a few seconds differing when executed).
That would help with this discussion too: http://lists.mysql.com/mysql/201015 ;)
Yes, it'd be sweet if that mysql internals guru revelead her/him-self from the cloud of guruness and spoke the true way of doing it. What pisses me off most is that 'grep -E "^word$| word$|^word | word " 2006/07/*/phrases |wc -l' is so much quicker than the db :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
