Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 '11 at 1:51
add comment

1 Answer

up vote 0 down vote accepted

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)
share|improve this answer
    
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 '11 at 19:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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