What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

Question

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been read. A user can read items, and then they don't appear anymore.

I have a table that handles each item that is read by a user. Previously it was like this:

id (INT) PRIMARY
user_id (INT)
item_sid (VARCHAR)

My first beginner mistake was to use an id, instead of putting an index on item_sid. id is completly useless, and should not be used.
Also for some queries, to avoid joins, I think it is better if I put in a feed_id column, to grab every item from a specific feed.
The new table should look like that:

user_id (INT)
item_sid (VARCHAR)
feed_id (INT)

Here are some example of queries running on this table:

• Get amount of items a user has read from a feed (count(item_sid) WHERE user_id = x and feed_id = x)
• Check if an item has been read (WHERE user_id = x AND item_sid = x)
• Delete an item that has been read
• List all items that have been read by the user (SELECT item_sid WHERE user_id = x)

I have two questions about this:
1) What type of indexes should I put on this table, I have not a very good understand yet of how every indexes work, I'm not sure if I should use fulltext or not on the item_sid
2) Am I doing it the correct way, or is there something better I could do ?

Thank you

Answer 1

This looks like a join table on Items and Users. You may find putting ID columns these two tables and using them in the join table works well. Feed would be a parent for Items, and you could make the primary key if Items a compound key including Feed Id.

For the table in question you likely want indexes on:

• User_Id, Feed_Id, Item_Id (Primary key searchable for user's data)
• Feed_Id, Item_Id (Foreign key from Items compound key)
• User_Id (Foreign key from Users, optional as included in the first index)