MySQL keyword frequency
Posted: Thu Jun 19, 2008 10:08 am
Hi,
I hope someone can help.
I am doing a mysql search on 3 columns within a table, and I am ranking the returned result and ordering by the total relevance. However, I would like to add another count to the relevance if the keyword appears in the field more than once.
Below is a simplified version.
SELECT *, (
(CASE WHEN title LIKE '%the%' THEN 2 ELSE 0 END) +
(CASE WHEN content LIKE '%the%' THEN 1 ELSE 0 END)
) as relevance
FROM content WHERE (page LIKE '%the%' OR title LIKE '%the%' OR content LIKE '%the%')
ORDER BY relevance DESC
So if 'the' appears in the 'title' column and 'content' column the result will be 3. But if 'the' appears three times in the 'content' column it won't total 3 and then add it to the 2 from 'title' to equal 5. But I would like it to total 5.
Hope that makes sense.
Thanks,
Eddie
I hope someone can help.
I am doing a mysql search on 3 columns within a table, and I am ranking the returned result and ordering by the total relevance. However, I would like to add another count to the relevance if the keyword appears in the field more than once.
Below is a simplified version.
SELECT *, (
(CASE WHEN title LIKE '%the%' THEN 2 ELSE 0 END) +
(CASE WHEN content LIKE '%the%' THEN 1 ELSE 0 END)
) as relevance
FROM content WHERE (page LIKE '%the%' OR title LIKE '%the%' OR content LIKE '%the%')
ORDER BY relevance DESC
So if 'the' appears in the 'title' column and 'content' column the result will be 3. But if 'the' appears three times in the 'content' column it won't total 3 and then add it to the 2 from 'title' to equal 5. But I would like it to total 5.
Hope that makes sense.
Thanks,
Eddie