We have a table for logging actions. The volume added up pretty fast. After about 3-4 months, we got 600M rows, we need to store the data for 1 year at least, so it is expected to host 2-3 billions rows.

The structure is not very complicated:

    CREATE TABLE `site_statistic` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `entityType` varchar(50) NOT NULL,
     `entityId` int(10) unsigned NOT NULL,
     `entityCount` int(10) unsigned NOT NULL DEFAULT '1',
     `timeStamp` int(10) unsigned NOT NULL,
     PRIMARY KEY (`id`),
     KEY `entity` (`entityType`,`timeStamp`,`entityId`)

Sample data:

  • when a user logs in: entityType = "user.login" , entityId = that user Id , entityCount= 1 most of the time
  • when a product is added: entityType = "product.add" , entityId = that productId , entityCount= 1
  • when a product is clicked: entityType = "product.clicked" , entityId = that productId , entityCount= 1
  • when an album is liked: entityType = "album .liked" , entityId = that album id..
  • so on and so forth.

Currently, data insert is not a problem (yet). But reading the data is very slow. It took 90+ seconds to query product activities for a given period of time:

            DATE_FORMAT(FROM_UNIXTIME(`timeStamp`), "%e") AS `category`,
            SUM(`entityCount`) as `count`
            `entityType` IN ('product.displayed','product.clicked','product.add')
            `timeStamp` >= 1527267600
            `timeStamp` <= 1527872399 AND entityId = 12807
        GROUP BY

Mysql crashed (or rather not responding) from time to time when many statistic reports are required at the same time.

Deleting the data (related to a product for example) is also very slow. We need to delete to save space...

What can we do to improve it, apart from disable real time report and send later by email?

P.s.: Our client can be considered small (few guys hosting a website with modest profit) not some crazy enterprise solution.

  • 1
    One of the standard solution is partitioning: you split your gigantic table in many smaller ones. You could either partition on the date, or (group of) user ids (entityId in your schema). Based on your query, a proper RDBMS and partitioning would make it read only a few tables and hence a few rows instead of basically all of them. I would however start by separating user actions and product actions as they are conceptually different, and may be attached to different retention policies. May 31 '18 at 21:52
  • @PatrickMevzek : advanced stuff I see. I first read those more than 10 years ago and never dig deep. It is time now I guess.... "start by separating user and product actions" => into multiple tables ? If so I don't think ppl here are gonna like that. Becoz not only of quite many existing code but also it allow dynamically add more entity anytime (e.g album, article, XYZ, etc.. ) . Thanks alot Jun 1 '18 at 9:01
  • Note that it is covered in the manual: dev.mysql.com/doc/refman/8.0/en/partitioning.html so it comes included with your database. Yes splitting in multiple tables would be both more logical and a path in solving your problem of volume. Jun 3 '18 at 23:04
  • @PhamH.An - Advanced? It simplifies things if you are not mixing logins with clicks.
    – Rick James
    Jun 4 '18 at 23:47
  • Use InnoDB.

  • Normalize entityType or turn it into an ENUM.

  • Change INT UNSIGNED (always 4 bytes) into a suitable sized int)

  • If the 3-column key is unique, then make it the PRIMARY KEY and toss id

  • id, if kept, will need to be BIGINT UNSIGNED -- you are getting too close to the 4 billion limit of INT UNSIGNED.

  • The optimal order for the index: (entityId, entityType, timeStamp), or possibly (entityId, timeStamp)

  • Minimize the number of indexes on a DataWarehouse table.

  • For purging data after a year, DELETE will be painful. Instead PARTITION BY RANGE and use months: http://mysql.rjweb.org/doc.php/partitionmaint

  • If you get in trouble with the ingestion rate: http://mysql.rjweb.org/doc.php/staging_table

  • If you keep id, then do

this trick:

PRIMARY KEY (entityId, timeStamp, id)

This, together with InnoDB, makes the data for that SELECT mostly 'clustered', hence faster to fetch.

But the big fix to that query, and similar "report" queries in a Data Warehouse (which what you have), comes from building and maintaining Summary table(s): http://mysql.rjweb.org/doc.php/summarytables

The summary table to support that would look something like:

CREATE TABLE summary (
    entityType VARCHAR... -- denormalized
    dy  -- timestamp truncated to the day
    sumCount  -- SUM(entityCount); use SUM(sumCount) to get weekly total (etc)
    PRIMARY KEY(entityid, entityType, dy)

See the link for various ways to keep the summary table updated.


Munch on what I said. Then think about how it splitting into multiple similar tables based on entityType.

One simplicity -- count is always "1" for some Types, so you don't need a count column (except in the Summary table for that Type).

  • U r right, delete is so slow. I tried it and should not try again... Your answer looks promising, I need sometime to implement it. I will come back when it is done and accept it. Jun 5 '18 at 19:06

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.