0

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

1
  • Otherwise wouldn't be easier to have a single column "id" that has both user_id and item_sid concatenated in it. That way it will be a unique column ?
    – xtrimsky
    Dec 30, 2011 at 1:51

1 Answer 1

0

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)
1
  • Thanks! Just to give some estimates about how much time indexes can save, the first db design took me about 38 seconds to execute on a 10,000 rows db. Now its less than a second :).
    – xtrimsky
    Dec 30, 2011 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.