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

  • 2
    I think this question would be better suited at the Database Administrators site. Voted to close/migrate.
    – jpw
    Commented Oct 23, 2014 at 0:54

1 Answer 1


Your query is presumably something like:

select a.*
from activities a
where user_is = 'abc' and time > somedate


where user_is = 'abc' and time > somedate and type = xyz

The best index for the first query is activities(user_is, time) and for the second activities(user_id, type, time).

Your Answer

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

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