0

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
  • Please show us the actual query and the SHOW CREATE TABLEs. There may be a simple solution. – Rick James Jul 16 '16 at 5:57
  • I updated my create and my count queries – nam ngo Jul 16 '16 at 15:30
0

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 '16 at 18:05
  • It takes time to scan through 2 million records. – Rick James Jul 17 '16 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 '16 at 17:53
0

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 '16 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 '16 at 19:04

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.