Recommend system

Implicit, content-based

[DNnetz]

I want to build a recommender system which is based on content - like Pandora.com's system.
For each user in my database I collect the tags of his favored articles. Based on these tags I want to recommend some articles to the specific user.
My problem starts when I want to read out the articles from the database (MySQL) with PHP.
I could read out all articles and then look whether there is a favored tag in or not. This way my script could run through all entries. But it would be inperformant. This procedure would cause high database load.
So my question is whether there is any algorithm which I could use to let my function iterate. Can you please help we solving this problem? Thanks in advance!

Here's a short example:
User X favors the tags "sports", "us elections" and "kobe bryant". In the database there are the following articles:
- Article Y1, tags: sports
- Article Y2, tags: none
- Article Y3, tags: kobe bryant, us elections
- Article Y5, tags: us elections
- Article Y6, tags: sports, kobe bryant, us elections
My function should sort them:
Y6, Y3, Y1, Y5

[phparion]

welcome to WebmasterWorld!

first of all there are different methods to handle this, first is to add a column for tags in the articles table and add a form field in the article posting form, where you ask the author to post the related tags / keywords for his article.

then it will be a matter of mysql SELECT e.g

SELECT * FROM articlesTable WHERE
tags='".$userTags."'";

this is the preferred method, as it is easy to handle in the site and very efficient. However, if you already have lots of articles posted in the database then FIRST BACKUP your database in a safe place, then add a new column or separate table, if normalization fan, and write a crawler for your own articles which crawls / searches database each article and fetch out words between heading tag, or bold tag or any other tag authors have used as important words for the articles.

Usually, authors put the words they want Search Engines to pick them for in H1/H2 etc and <strong> etc and in start of the contents, good writes use those words in the articles titles too.

So you need to think from an authors prespective and knit the logic for your crawler which reads all articles and separate the tags from them and add each article ID (if a separate tags table) with each set of tags. So that you can use MySQL simple queries to achieve your goal.

I hope it helps.

[DNnetz]

Thanks for your detailed answer!
But I think you misunderstood me, maybe because my description of the problem was a bit unexact.

I already have extracted the tags and separated them from the articles. So my database structure is as follows:

TABLE interests:
user / VARCHAR(32) [ID of the user for this entry]
tag / VARCHAR(255) [topic which the recommendations should be based on]
count / INT(11) [number of articles of this topic which the user voted for; indicating how important the topic is to the user]

TABLE news:
ids / VARCHAR(32) [ID of the current news entry]
title / VARCHAR(255) [title of the news article]
rank / INT(11) [indicating how interesting/important the article is in general (for all users); the higher the more interesting]

TABLE tags:
tag / VARCHAR(255) [name of the topic]
news / VARCHAR(32) [news article ID of news table this entry belongs to]

My problem: There is a given user id X. So I can select all tags user X is interested in by using this query:
"SELECT tag, count FROM interests WHERE user = 'X'"
Based on this taglist with a value for the importance (count), I want to find all articles which could also be interesting for the user X.

[phparion]

bro your database structure is not good, in my opinion.

first why tag of table interests and tags-table are both varchar?

you should keep a tags table like

Table Tags,
tagID (PK)
tag / VARCHAR(255) [name of the topic]
userID (FK)

you don't need NEWS ID in TAGS table but you need it tagID in News Table i.e

Table news:

newsID / (PK) [ID of the current news entry]
title / VARCHAR(255) [title of the news article]
rank / INT(11) [indicating how interesting/important the article is in general (for all users); the higher the more interesting]

tagID / (FK)

and third table could be

Table Interes:

userID / (FK) [ID of the user for this entry]
tag / VARCHAR(255) [topic which the recommendations should be based on]
count / INT(11) [number of articles of this topic which the user voted for; indicating how important the topic is to the user]

** what is the different between tag column of this table and Tags table?

Now what is the common thing between three tables?

** userid is connects INTEREST and TAGS and TAGS's tagID is present in NEWS table

... which is your target entity, so use any MySQL JOIN or simple WHERE clause e.g

SELECT i.tag,n.title FROM interests i, news n, tags t WHERE i.userID=t.userID AND n.tagID=t.tagID

i have not tried this query anywhere and writing in hassle so not sure if it is cent percent correct. but this is the idea you can improvise with.

i hope it helps

(( i had messed up sql query and connection logic, i need to sleep now.. had a long coding day :-s)

[SarK0Y]

Hi, DNnetz.
favored articles you can store to cookies. for optimize looking for recommend articles you should design your db with fit structure. may create so: all data are splited on the subjects (main table contains 1subj, 2subj etc.; next table 11subj, 12subj etc.) each table creates by necessity. may take more speed so: recommend articles issue to user with date range but not all unreaded pages.

[FourDegreez]

Hmm.

SELECT a.news_id, COUNT(*)
FROM news a, tags b, interests c
WHERE c.tag=b.tag AND b.news_id=a.news_id AND c.user = 123
GROUP BY a.news_id
ORDER BY COUNT(*) DESC

I think that pretty much does what you want.

[DNnetz]

Thank you, phparion and SarK0Y! Your tips helped me to improve the performance and scalability of my website.

@FourDegreez: Thank you very much! That is exactly what I was looking for. I've already tested it and it works well.
The only thing you didn't include into your query is the column "count" of the table "interests". So my last question is: Could I include it by multiplying the COUNT(*) in the order-clause by the count? So my query would look like this:

SELECT a.news_id, COUNT(*)
FROM news a, tags b, interests c
WHERE c.tag=b.tag AND b.news_id=a.news_id AND c.user = 123
GROUP BY a.news_id
ORDER BY (COUNT(*)*c.count) DESC

[SarK0Y]

Hi, phparion.
sorry, i don't understand meaning of the 'TAGS' table - two other tables quite enough, imho. and table 'NEWS' must have timestamp article's issue otherwise we have searching all unreaded articles.