0

For my flask app I need to track impressions, clicks on images. The current implementation that I have is impressions table where I store impression ip, useragent, geo location and all the relationships that I would like to have. So every impression has it's own row. Mainly I'm interested in count of impressions by hour, day, date range, month.

I tested this implementation by inserting 2.5M rows in my impression table. The problem with this implementation is that count query are slow when I try to get impressions count for whole month.(5 seconds per query) Index doesn't help much here. I tried different query's and MySQL db optimization and results are not satisfactory.

In my opinion I will have to change the way I handle impression tracking. I was thinking to create a table that holds impressions count for every hour. When ever new impression happens I would just increment counter for that hour.

But before I go and rewrite bunch of code and make mistake again.

I'm asking here how other web app handle tracking impressions and get results so fast? Or what is the correct way to store impressions?

Hopefully some can guide me to fix this problem.

Best regards

4
  • Ok I will try to update MySQL on server. I already tried on local host and didn't saw any big difference. At the beginning of the project I wanted to use PostgreSQL but I decide for MySQL because is so widely available. Will PostgreSQL speed up query's so much ? Is there no option to import MySQL db to PostgreSQL ?
    – martinktm
    Aug 17, 2020 at 18:19
  • For the aggregates do you want sliding or tumbling windows? Aug 18, 2020 at 6:42
  • When you count an hour is it 10am to 11am, 11am to 12pm, 12pm to 1pm etc. or is it "right now" (1:17pm) to "an hour ago" (12:17pm)? Is last month 1 July to 31 July or 19 July to 19 August (today). Aug 19, 2020 at 0:44
  • @MichaelGreen Currently I implemented by by date and time 10am to 11 am and just increment counter by 1 when impression happens. But in future I would like to store every impression separately by datetime and all the data that belongs to this impression.
    – martinktm
    Aug 20, 2020 at 16:39

1 Answer 1

1

Summary tables. Separate table for counts.

Are you keeping information about each impression? Or just a counter? It makes a big difference. But we can solve it either way.

If you go with a table with just daily counter:

CREATE TABLE FooImpressions (
    foo_id  INT UNSIGNED NOT NULL,  -- same as main table, but not AUTO_INCREMENT
    date DATE NOT NULL,
    counter INT UNSIGNED NOT NULL,
    PRIMARY KEY(foo_id, date)
) ENGINE=InnoDB;

And

INSERT INTO FooImpressions (foo_id, date, counter)
    VALUES (?, ?, 1)
    ON DUPLICATE KEY UPDATE
        counter = VALUES(counter) + 1;

This automatically starts a new row for each Foo each day (as needed).

Then, to get last weeks counts:

SELECT foo_id, SUM(counter) AS impressions
    FROM FooImpressions
    WHERE date >= CURDATE() - INTERVAL 7 DAY
      AND date  < CURDATE()
    GROUP BY foo_id;

Of course, the date range can be changed to handle any consecutive date range.

There are variants on this for more complex situations and for situations that need more performance. But this is a simple "summary table" together with its maintenance and its usage.

Yes, one variation would have DATETIME and truncate that to the hour. Don't have both an hourly summary table and a daily one until you have demonstrated that the hourly one is not fast enough.

If you need up-to-the-minute counts, some Summary techniques do not provide such (because the do nightly or hourly batch updates for speed); the use of IODKU is always up-to-the-minute.

Even if you have another table with more info on the impressions, have this Summary table for speed of reporting.

More discussion: http://localhost/rjweb/mysql/doc.php/summarytables

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.