Mysql – Query optimization with conditional group by and where

MySQLperformancequery-performance

I have a DB with events, workshops in each event (1:N), participants in each workshop/event(N:M), the role of a participant in each workshop, and a table (letture) with access control for each participant in the workshop.

We create a unique QR code for participants/workshops. With this QR code, 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 > 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 > i1.datalettura
    AND i1.id_event = i3.id_event
    AND i1.id_sala = i3.id_sala
    AND i3.entrata = 2
    AND i3.datalettura < 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, especially 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;

Best Answer

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 > 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 ....

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Mysql – Adding index to large thesql tables

Related Question