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

database-designindexMySQLPHP

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

Best Answer

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)
Related Question