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 ?