domenica 15 settembre 2013

MySQL: VIEW, UNION, JOIN... and IF

Nel mio lavoro utilizzo spesso MySQL. Non sono un esperto, ma ne faccio largo uso. Stavo affrontando un certo problema che si è risolveva banalmente creando tale vista[1]:

 CREATE VIEW `impiantisoggetti` AS
     SELECT
         `collegamento`.`id` AS `id_collegamento`,
         `collegamento`.`id_user` AS `id_user`,
         `utente`.`codice` AS `codice`,
         `impianto`.`id` AS `id_impianto`,
       FROM ( (`impianto`
         left join `collegamento`
           on (`impianto`.`id` = `collegamento`.`id_impianto` ) )
         left join `utente`
           on (`utente`.`id` = `collegamento`.`id_user`)
         inner join impiantisoggettilast
           on (`collegamento`.`id` = `impiantisoggettilast`.`maxID`)
     )
   UNION
     SELECT
         `collegamento`.`id` AS `id_collegamento`,
         `collegamento`.`id_manutentore` AS `id_user`,
         `manutentore`.`codice` AS `codice`,
         `impianto`.`id` AS `id_impianto`,
       FROM ( (`impianto`
         left join `collegamento`
           on (`impianto`.`id` = `collegamento`.`id_impianto` ) )
         left join `utente`
           on (`manutentore`.`id` = `collegamento`.`id_manutentore`)
         inner join impiantisoggettilast
           on (`collegamento`.`id` = `impiantisoggettilast`.`maxID`)
     )
Banalmente, una VIEW formata da una UNION di JOIN.
Non ci sarebbe molto da dire. Creata la vista, la provo e noto che le prestazioni decadono di un fattore 100.
Isolando le query della UNION, creando tali viste impiegano su dati reali una frazione attorno al mezzo secondo in un caso, un decimo nel secondo. La VIEW formata dalla UNION mostrata impiegava circa 40 secondi.
Leggendo un po' di documentazione, ho scoperto che creando le VIEW l'algoritmo utilizzato è MERGE oppure TEMPTABLE. Se non definito, nella costruzione il server cerca di utilizzare MERGE altrimenti TEMPTABLE.
Facendola breve, se si utilizza MERGE i filtri possono essere applicati direttamente, altrimenti sarà utilizzato TEMPTABLE in cui sarà necessario creare una tabella temporanea a cui successivamente si applicherà il filtro. Si intuisce facilmente la caduta prestazionale.
Ok, requisito affinché si possa utilizzare MERGE nella creazione della VIEW è che non si utilizzino funzioni o aggregazioni sulle colonne, insomma è necessario utilizzare le colonne in modalità row, così come sono. La UNION è male, nel senso non permette l'utilizzo dell'algoritmo MERGE.
Come si può risolvere. In due modi.
Il primo è modificare l'applicativo. Spesso è facile quindi non è un'opzione necessariamente da scartare.
La via preferibile è la seconda proprio perché è più semplice e più immediata... è necessario utilizzare un accorgimento lato sql con gli IF che personalmente non avevo mai utilizzato. Ecco la loro applicazione, molto intuitiva e pertanto non spiegherò ma è utile mostrare:

 CREATE VIEW `impiantisoggetti` AS
    SELECT
        `collegamento`.`id` AS `id_collegamento`,
        IF (`collegamento`.`id_user` IS NOT NULL, `collegamento`.`id_user`, `collegamento`.`id_manutentore`) AS `id_user`,
        IF (`collegamento`.`id_user` IS NOT NULL, `utente`.`codice`, `manutentore`.`codice`) AS `codice_utente`,
        `impianto`.`id` AS `id_impianto`,
    from ( (`impianto`
        left join `collegamento`
            on (`impianto`.`id` = `collegamento`.`id_impianto` ) )
        left join `utente`
            on (`collegamento`.`id_user` IS NOT NULL AND `utente`.`id` = `collegamento`.`id_user`)
        left join `manutentore`
            on (`collegamento`.`id_manutentore` IS NOT NULL AND `manutentore`.`id` = `collegamento`.`id_manutentore`)
        inner join impiantisoggettilast
            on (`collegamento`.`id` = `impiantisoggettilast`.`maxID`)
    )

Note:
[1] -> ovviamente è stato presentato una versione semplificata

Nessun commento:

Posta un commento