Query optimization with conditional group by and where I've a DB with events, workshops in each event (1:N), participants in each workshop/event(N:M), role of participant in each workshop, and a table (letture) with access control for each participant in workshop.

We create a unique qrcode for participant/workshop. With this qrcode we can identify who went to which workshop and we can calculate for how many hours he remained.

But not all workshops need this in/out (entrata=1/2) value, for some are sufficient check who are present (entrata=0).

To print the final certificate for every participant in every event/workshop I try to populate the table attestati_dati with this query :

TRUNCATE attestati_dati; 
INSERT INTO attestati_dati 
(id_participant,id_event,id_workshop,id_role,id_attestato,ore_totali)  
SELECT * FROM 
(
   SELECT 
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
     SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,
             i1.datalettura,
             IFNULL(i2.datalettura, i1.datalettura)))) AS ore_totali 
FROM
  letture AS i1
     LEFT JOIN
 letture AS i2 ON (i1.qrcode = i2.qrcode
     AND i2.datalettura &gt; i1.datalettura
    AND i1.id_event = i2.id_event
    AND i1.id_sala = i2.id_sala
    AND i2.entrata = 2)
    LEFT JOIN
letture AS i3 ON (i3.qrcode = i2.qrcode
    AND i3.datalettura &gt; i1.datalettura
    AND i1.id_event = i3.id_event
    AND i1.id_sala = i3.id_sala
    AND i3.entrata = 2
    AND i3.datalettura &lt; i2.datalettura)
    CROSS JOIN
event_participants ep ON ep.qrcode = i1.qrcode
    AND ep.id_event = i1.id_event
    LEFT JOIN
participants p ON p.id_participant = ep.id_participant
    LEFT JOIN
roles r ON ep.id_role = r.id_role
    LEFT JOIN
events e ON i1.id_event = e.id_event
    LEFT JOIN
workshop_participant wp ON wp.id_participant = p.id_participant
    AND wp.id_workshop = i1.id_sala
WHERE
 i1.entrata = 1
     AND i3.datalettura IS NULL
     AND i1.id_Event = 52
GROUP BY i1.qrcode
UNION SELECT 
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
    SEC_TO_TIME(0) AS ore_totali
FROM
 letture AS i1
     CROSS JOIN
 event_participants ep ON ep.qrcode = i1.qrcode
     AND ep.id_event = i1.id_event
     LEFT JOIN
 participants p ON p.id_participant = ep.id_participant
     LEFT JOIN
 roles r ON ep.id_role = r.id_role
     LEFT JOIN
 events e ON i1.id_event = e.id_event
     LEFT JOIN
 workshop_participant wp ON wp.id_participant = p.id_participant
     AND wp.id_workshop = i1.id_sala
WHERE
    i1.entrata = 0 AND i1.id_Event = 52
GROUP BY i1.qrcode , DAYOFMONTH(i1.datalettura)
) A;


I'd like to improve this query because this is the best I can do to solve this problem, but I think that is optimizable, specially in the group by/where/joins part.

Show Create table:

  CREATE TABLE `attestati_dati` (
 `id_attestati_participants` int(11) NOT NULL AUTO_INCREMENT,
 `id_participant` int(11) DEFAULT NULL,
 `id_event` int(11) DEFAULT NULL,
 `id_workshop` int(11) DEFAULT NULL,
 `id_role` int(11) DEFAULT NULL,
 `id_attestato` int(11) DEFAULT NULL,
 `ore_totali` time DEFAULT NULL,
  PRIMARY KEY (`id_attestati_participants`)
 ) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
e and wp are not used, so remove the `LEFT JOINs to them.

Some composite indexes needed:

letture:  INDEX(id_event, entrata, qrcode, id_sala, datalectura)
ep:  INDEX(qrcode, id_event)  -- in either order


My suggested index for letture helps with all 3 uses of that table, and in different ways.  The order of the columns is critical, except that the first two can be swapped.

If AND  i2.datalettura &gt; i1.datalettura is part of doing a "groupwise max", then it is especially inefficient.  This discusses much faster ways.

AND  i3.datalettura IS NULL and its LEFT JOIN can probably be replaced by NOT EXISTS ( SELECT 1 ... )

The CROSS JOIN is technically an INNER JOIN, but since MySQL ignores CROSS and INNER, either of those works the same.  Ditto for plain JOIN.

The index recommendations come from looking at the WHERE clauses, and noting that nothing can be done about the GROUP BY.  More discussion here.

If you need to discuss this further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....