For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table).

And its really time consuming when it comes to million records data. So I decided to use mySql Replicate to create a slave db for my reporting purpose. But I'm still struggling with these things:

  1. Are you willing to duplicate a part of your db in exchange for query speed ?
  2. Is there any way to add data to user_type column automatically in 'user_activity' table (slave db) when they are syncing?
  3. And how to optimize database for reading purpose? (reporting data, analytics,..)

update: this is my create sql

Table user

CREATE TABLE `library_th`.`user` ( 
`username` VARCHAR(100) NOT NULL , 
`password` VARCHAR(32) NOT NULL , 
`user_type` TINYINT NOT NULL DEFAULT '0' COMMENT 'this user is created automatically by system or by them himself?' , 
) ENGINE = InnoDB;

Table user_activity:

CREATE TABLE IF NOT EXISTS `user_activity` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `action` int(11) NOT NULL DEFAULT '0' COMMENT 'what activity did user do?',
  `datetime_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
ALTER TABLE `user_activity`
  ADD KEY `user_id` (`user_id`);

and my count query that I'm wanna use is:

select count(ua.id) as `total`, u.user_type, ua.action from user_activity ua
inner join user u on ua.user_id = u.id
group by ua.action, u.user_type
  • Please show us the actual query and the SHOW CREATE TABLEs. There may be a simple solution.
    – Rick James
    Jul 16, 2016 at 5:57
  • I updated my create and my count queries
    – nam ngo
    Jul 16, 2016 at 15:30

2 Answers 2


Give this a try:

Add this composite index to user_activity: INDEX(user_id, action). Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;
  • Its faster than my old query but its still slow for 2 million records. It took ~7s to execute for the very first time.
    – nam ngo
    Jul 16, 2016 at 18:05
  • It takes time to scan through 2 million records.
    – Rick James
    Jul 17, 2016 at 16:18
  • Yeah, but is there anyway to speed up that scan? like accept duplicating data by putting 'user_type' in 'user_activity' table or other methods...
    – nam ngo
    Jul 17, 2016 at 17:53

In Data Warehousing, "Summary tables" are very important for performance.

A Summary table contains counts and subtotals for each day (or other time period). "Reports", such as your query are then run using a summary table, and can run much faster than running against the 'raw' data.

For your case, build and incrementally maintain a table with, say:
day, user_type, action, count
The first 3 columns would be the PRIMARY KEY, the last would be the subtotal.

You would need an index on datetime_created to make it more efficiently to do the nightly update.

  • So lets me make it clear, i have to create a table which has 4 fields, and for every insert query to my user_activity table, increase the appropriate 'count' field to 1, or i can do a cron-job to count it at the end of the day?
    – nam ngo
    Jul 17, 2016 at 17:50
  • Plan A: INSERT .. ON DUPLICATE KEY UPDATE... to insert of bump the count. Plan B: nightly cron job to compute the day's stats. Plan C: (there may be other ways.) More discussion.
    – Rick James
    Jul 17, 2016 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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