Mysql – How to improve query count execution with theSql replicate

countMySQLoptimizationreplication

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

Best Answer

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;
Related Question