0

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

migrated from stackoverflow.com Oct 28 '14 at 10:27

This question came from our site for professional and enthusiast programmers.

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

Your query is presumably something like:

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

or

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, privacy policy and cookie policy

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