How to improve query count execution with mySql replicate?

Question

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` ( 
`id` INT UNSIGNED NOT NULL , 
`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?' , 
PRIMARY KEY (`id`)
) 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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

Answer 1

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;

Answer 2

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.