469,913 Members | 2,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

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.

Dec 19 '06 #1
1 2319

ra**********@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;

Dec 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Andreas Falck | last post: by
reply views Thread by Daniel | last post: by
6 posts views Thread by Silly | last post: by
3 posts views Thread by Reddy | last post: by
3 posts views Thread by Sonnich | last post: by
4 posts views Thread by Huaer.XC | last post: by
15 posts views Thread by kenneth | last post: by
14 posts views Thread by andreyvul | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.