I found some articles and questions on this issue but not sure if I got the answer. very new to db modeling so please bear with me My use case is:
- Save user activities: I'm doing this by creating a one table and saving all user activities. The table has a ID, user_id(foreign key), time(Datetime), type(varchar), description
- Query the table for activities for a user for a time frame and potentially for a activity type.
My concern is the performance when the table has a large number of rows. My steps that I think will help improve is
- index the user_id
- index the time
Then I can query for "user_is = 'abc' and time > somedate" or "user_is = 'abc' and time > somedate and type = xyz"
should I index type too? will having too many index have performance issues (when inserting new row or querying)? What else can I do. Any advise will be greatly appreciated. Thanks in advance