MySQL query performance of Date sorted data

MySQLperformance

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

Best Answer

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).

Related Solutions

Mysql – Help with complicated MySQL query

Have you considered using a Stored Function ?

You can split the union into your individual queries and if it passes any one of the three, return count(*). Otherwise, return 0

Some restrictions on stored functions

I recommended function over procedure because it's invoked like:

SELECT myfunc(userID, eventID);

and your query doesn't look like it needs a resultset returned, just a single value. If it needs a resultset, you would need a procedure (as noted in the restrictions documentation)

Sql-server – Optimizing an audit table for inserts and deletes

Typically to optimize inserts/deletes for such a table you would cluster on the datetime column. (You are tracking when these events happen, I presume.)

This way, inserts go to the end of the table always, minimizing the "bad" kind of page splits. Deleting or archiving data is easy because the clustered index will support range deletes, and these operations will not typically be locking the pages where you're performing inserts (unless you have escalation for some reason).

You will probably want other indexes like what you're describing to support queries, and maintenance of these of course will interfere somewhat with DML.

I'm not sure I understand why you only want to keep a certain number of rows for any UserId/EventId combination. If one user does a whole bunch of stuff today, do you really want to remove all the stuff they did yesterday, last week, etc., while you keep older data for users that are less active? Wouldn't it make more sense to just keep data based on time (e.g. retain two weeks of history)?

I'm also not sure why the delete absolutely needs to be coupled to the insert. Can't the delete be deferred to a background process instead of holding up the insert transaction?

Best Answer

Related Solutions

Mysql – Help with complicated MySQL query

Sql-server – Optimizing an audit table for inserts and deletes

Related Question