Getting the query right?

``
CREATE TABLE Action (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
created_By_Id int(10) unsigned NOT NULL,
name varchar(60) NOT NULL,
created_At datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
notes varchar(400) DEFAULT NULL,
PRIMARY KEY (id),

KEY action_created_By_Id_fk (created_By_Id),

CONSTRAINT action_created_By_Id_fk FOREIGN KEY (created_By_Id) REFERENCES Employee (id) ON DELETE CASCADE,

) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1

CREATE TABLE ActionAssignedTo (
action_Id int(10) unsigned DEFAULT NULL,
assignee_Id int(10) unsigned DEFAULT NULL,
-- KEY actionassignedto_assignee_id_foreign (assignee_Id),
-- replaced by:
UNIQUE KEY actionassignedto_action_id_assignee_id_unique (action_Id,assignee_Id),

KEY actionassignedto_action_id_foreign (action_Id),
CONSTRAINT ActionAssignedTo_ibfk_1 FOREIGN KEY (assignee_Id) REFERENCES Employee (id) ON DELETE CASCADE,
CONSTRAINT ActionAssignedTo_ibfk_2 FOREIGN KEY (action_Id) REFERENCES Action (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE Employee (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
vendor_Id int(10) unsigned DEFAULT NULL,
name varchar(40) NOT NULL,
PRIMARY KEY (id),
KEY employee_vendor_id_foreign (vendor_Id),
CONSTRAINT employee_vendor_id_foreign FOREIGN KEY (vendor_Id) REFERENCES Vendor (vendor_Id)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

CREATE TABLE ActionComment (
comment_Id int(10) unsigned NOT NULL AUTO_INCREMENT,
commentor_Id int(10) unsigned NOT NULL,
action_Id int(10) unsigned NOT NULL,
comment varchar(255) DEFAULT NULL,
created_ActionComment_At datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (comment_Id),
KEY actioncomment_commentor_id_foreign (commentor_Id),
KEY actioncomment_action_id_foreign (action_Id),
CONSTRAINT actioncomment_action_id_foreign

You seem to have 2 questions.

-
Is there something better than DISTINCT in GROUP_CONCAT()? No. Use it when you need it.

-
Can the query be sped up or otherwise improved?

Don't use IN ( SELECT ... ), use EXISTS or JOIN.

JOIN + GROUP BY smells like the inefficient "explode-implode" pattern. First you build a large set or rows (from the joins), then you collapse them back to one row per row in the first table (via group by). It is often grossly inefficient, leads to inflated COUNT() and SUM(), and in your case the need for DISTINCT.

Indexes:

ActionAssignedTo:  (action_Id, assignee_Id) -- in this order; smells like the PK?

ActionAssignedTo smells like a many-to-many table. It really needs PRIMARY KEY(action_Id, assignee_Id). This, alone, may help performance. More discussion

What do you want to happen if there is nothing in ActionComment for a given action_Id? Seems like the JSON will be mangled. If not, get rid of the LEFT JOIN and change

concat( '[', ... ) as comments

into

( SELECT concat( '[', ... ) FROM ActionComment ) as comments

If there is a possibility of no rows, tack on an IFNULL or something to avoid a mess.

Please provide EXPLAIN SELECT ...

Before my suggested changes, here is a prettyprint of the code:

select  A.name,
        group_concat(E.name) as assignedTo,
        group_concat( E.id) as assignedToId,
        concat( '[', group_concat(
               json_object( 'comment', AC.comment, 'comDate',
                            AC.created_ActionComment_At)
                    order by  AC.created_ActionComment_At asc) , ']'
              ) as comments,
    from  Action AS A
    inner join  Employee AS E  ON A.created_By_Id = E.id
      and  E.vendor_Id in (
        SELECT  vendor_Id
            from  Employee
            where  vendor_Id = 2
              and  id = 2 )
    inner join  ActionAssignedTo AS To1  ON A.id = To1.action_Id
      and  To1.action_Id = 1
      and  To1.assignee_Id = 2 // Business rule check
    inner join  ActionAssignedTo AS To2 as  ON To1.action_Id = To2.action_Id
    inner join  Employee AS E as AssigneeNameTable AS E  ON To2.assignee_Id = E.Id
    left join  ActionComment AS AC  ON A.id = AC.action_Id
    where  A.deleted_At is null
    group by  A.id

There may be more to improve; this is rather complex.

More

Shouldn't both of the columns in ActionAssignedTo be NOT NULL??

StackExchange Database Administrators Q#211121, answer score: 2