optimize header detail join group by sum

Question

I have two tables, that are Activities and ActivityDetails as below

create table Activities
(
    Id char(36) primary key,
    BranchId char(36) not null,
    ReferenceBranchId char(36) null,
    Date datetime not null,
    Type varchar(100) not null, -- Production, Delivery, Reception, SalesOrder
    Total decimal(16, 2) not null,
    Note varchar(400) null,
    IsActive bit(1) not null,
    CreatedById char(36) not null,
    CreatedDate datetime not null,
    ModifiedById char(36) null,
    ModifiedDate datetime null,
    constraint foreign key (BranchId) references Branches(Id) on delete cascade on update cascade,
    constraint foreign key (ReferenceBranchId) references Branches(Id) on delete cascade on update cascade,
    constraint foreign key (CreatedById) references Users(Id) on delete cascade on update cascade,
    constraint foreign key (ModifiedById) references Users(Id)
);

create table ActivityDetails
(
    Id char(36) primary key,
    ActivityId char(36) not null,
    No int not null,
    ItemId char(36) not null,
    Qty decimal(16,2) not null,
    IsActive bit(1) not null,
    CreatedById char(36) not null,
    CreatedDate datetime not null,
    ModifiedById char(36) null,
    ModifiedDate datetime null,
    constraint foreign key (ActivityId) references Activities(Id) on delete cascade on update cascade,
    constraint foreign key (ItemId) references Items(Id) on delete cascade on update cascade,
    constraint foreign key (CreatedById) references Users(Id) on delete cascade on update cascade,
    constraint foreign key (ModifiedById) references Users(Id)  
);

and a query as below

select
    Activities.BranchId, Activities.Type, ActivityDetails.ItemId,
    sum(ActivityDetails.Qty)
from        Activities 
join        ActivityDetails
    on Activities.Id = ActivityDetails.ActivityId and ActivityDetails.IsActive = 1
where       Activities.IsActive = 1
group by    Activities.BranchId, Activities.Type, ActivityDetails.ItemId;

The query causes timeout on the workbench.

Error Code: 2013. Lost connection to MySQL server during query  600.495 sec

The Activities has ~60.000 rows and the ActivityDetails has ~225.000 rows.

I already tried to add index, but it seems that it has no effect.

alter table Activities add index BranchIdTypeIdx (BranchId, Type);
alter table ActivityDetails add index ItemIdQtyIdx (ItemId, Qty);

The explain of the query is as below

 id | select_type | table            | partitions  | type | possible_keys | key        | key_len | ref           | rows    | filtered | Extra
 ---|-------------|------------------|-------------|------|---------------|------------|---------|---------------|---------|----------|---------------------------------------------
 1  | SIMPLE      | Activities       | null        | ALL  | PRIMARY       | null       |    null | null          |   58623 |    50.00 | Using where; Using temporary; Using filesort
 1  | SIMPLE      | ActivityDetails  | null        | ref  | ActivityId    | ActivityId |     108 | Activities.Id |       3 |    50.00 | Using where

What do I need to do to optimize the query (at least it doesn't timeout)?

Add index or partition on isactive. The other indexes may be of little use — Ab Bennett, Nov 19 '17 at 07:44
See this: [Error Code: 2013. Lost connection to MySQL server during query](https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query) — krokodilko, Nov 19 '17 at 08:21
@krokodilko yeah it changes the default timeout setting, do u know how to optimize / speed up the query? — bbsd, Nov 19 '17 at 09:30
@AbBennett I tried adding the IsActive as composite index, but it seems that is has no effect, still slow ~12 mins — bbsd, Nov 19 '17 at 09:31
What's the count of rows in each table , active vs non active — Ab Bennett, Nov 19 '17 at 10:56
@AbBennett inactive Activities ~1.500 and active Activities ~58.500, inactive ActivityDetails ~100 and active ActivityDetails ~225.000 — bbsd, Nov 19 '17 at 11:21
Ok, well no indexes are really going to help, I don't think there is much you can do query wise, I don't know what to suggest. Maybe the MySQL settings need ramping up — Ab Bennett, Nov 19 '17 at 11:47

score 0 · Accepted Answer · answered Nov 19 '17 at 19:20

I decided to cache the query first and run it on background service daily. The only drawback is the result is not realtime if the cache is not up to date.

delete from ItemStatsOnProgress;
insert ItemStatsOnProgress
select
    Activities.BranchId,
    ActivityDetails.ItemId,
    sum(case when Type = 'Production' then ActivityDetails.Qty else 0 end),
    sum(case when Type = 'Delivery' then ActivityDetails.Qty else 0 end),
    sum(case when Type = 'Reception' then ActivityDetails.Qty else 0 end),
    sum(case when Type = 'SalesOrder' then ActivityDetails.Qty else 0 end)
from        Activities 
join        ActivityDetails
    on Activities.Id = ActivityDetails.ActivityId and ActivityDetails.IsActive = 1
where       Activities.IsActive = 1
group by    Activities.BranchId, ActivityDetails.ItemId;

delete from ItemStats;
insert ItemStats select * from ItemStatsOnProgress;

optimize header detail join group by sum

1 Answers1