Optimize this!

Please help me optimize this:

I have a table with columns: headlineid, keyword.
headlineid+keyword combination is unique.

Relationship between headline and keyword is many-to-many. i.e.,
headlines can have many keywords. keywords can be associated with many
headlines.

Keywords in the same headline are considered "related".

Here's the query to find out which keywords 'hello' is related to and
sort desc on number of occurrences of the related keywords:

select keyword, count(keyword) as keywordcount from tags where
headlineid in (select distinct(headlineid) from tags where keyword =
'hello') AND keyword <> 'hello' group by keyword order by keywordcount
desc limit 0, 5;

This query takes a lot of time. Need help with optimization. All help
is appreciated.

rajeshkapadi@gmail.com wrote:
> Please help me optimize this:
>
> I have a table with columns: headlineid, keyword.
> headlineid+keyword combination is unique.
>
> Relationship between headline and keyword is many-to-many. i.e.,
> headlines can have many keywords. keywords can be associated with many
> headlines.
>
> Keywords in the same headline are considered "related".
>
> Here's the query to find out which keywords 'hello' is related to and
> sort desc on number of occurrences of the related keywords:
>
> select keyword, count(keyword) as keywordcount from tags where
> headlineid in (select distinct(headlineid) from tags where keyword =
> 'hello') AND keyword <> 'hello' group by keyword order by keywordcount
> desc limit 0, 5;
>
> This query takes a lot of time. Need help with optimization. All help
> is appreciated.
Try turning it into a join (example below), this usually helps speed up
this sort of thing.
Also build 2 indexes, one beginning with keyword the other beginning
with headlineid.

SELECT t2.keyword, count( t2.keyword ) AS keywordcount
FROM tags t1
JOIN tags t2 ON t1.headlineid = t2.headlineid AND t2.keyword <> 'hello'
WHERE t1.keyword = 'hello'
GROUP BY keyword
ORDER BY keywordcount DESC
LIMIT 0 , 5;