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:
- Are you willing to duplicate a part of your db in exchange for query speed ?
- Is there any way to add data to user_type column automatically in 'user_activity' table (slave db) when they are syncing?
- And how to optimize database for reading purpose? (reporting data, analytics,..)
this is my create sql
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;
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