0

Currently I was doing this:

I have a table called tracking_log where I am inserting a row whenever a user is using our app. We use this table to calculate users that are sticking with us and continuing to use the software.

This was working OK. But now I also want to see the number of active users for:

  • each day since launch
  • week-wise since launch
  • month wise since launch.

The table structure is something like:

tracking_log

- user_id   (integer, pk)
- when_used (timestamp)
- event     (event-type triggered by user. Kept for future usage.)

Our definition for "Active Users On a Day d1": users who had signed up earlier than (d1-15) days and has used the product within (d1-7) days.

The tracking_log table has around 500K records and counting. I was writing MySQL queries to calculate the above numbers but they are turning out to be very slow.

What is the best way to implement it? Are there any existing solutions to generate such reports with less effort?

Sabya
  • 11,534
  • 17
  • 67
  • 94
  • If you have 500K rows you are doing it wrong. – Dejan Marjanović Jul 20 '11 at 06:40
  • @webarto: Why? One row is inserted every time a user is using the app. These many rows are accumulated since 2 years. – Sabya Jul 20 '11 at 06:57
  • I understand, but if you have 1 000 000 users, just image how fast that 500K number will grow, and how much will it take for your queries. You should calculate data for each day and empty this tracking_log, let's say, today we had 1234 users, later you can use that same data for weeks and months. It is faster to add 7 (days) numbers than 10000 (no of users for 7 days) numbers. – Dejan Marjanović Jul 20 '11 at 07:04
  • It is OK to make "summary" tables without deleting the original data and report using the "summary" tables. But I also want to keep the original data in case we want to generate some other reports. – Sabya Jul 20 '11 at 07:25

0 Answers0