Do you have a question? Post it now! No Registration Necessary. Now with pictures!
December 19, 2006, 3:07 am
rate this thread
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
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
- Captain Paralytic
December 19, 2006, 3:06 pm
Re: Optimize this!
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
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;
- » Full text index before or after non-clustered index
- — Next thread in » MySQL Database Forum
- » SELECT multiple rows with average of a range of values
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum