La strategia DuplicateWeedout

DuplicateWeedout è una strategia di esecuzione per le subquery di tipo semi-join.

L'idea

L'idea è eseguire la semi-join come se fosse una normale inner join, e poi eliminare le combinazioni di record duplicate usando una tabella temporanea.

Si supponga di avere una query con la quale si cercano i Paesi aventi più del 33% percento della loro popolazione in una grande città:

select * 
from Country 
where 
   Country.code IN (select City.Country
                    from City 
                    where 
                      City.Population > 0.33 * Country.Population and 
                      City.Population > 1*1000*1000);

Prima si esegue una normale inner join tra le tabelle City e Country:

La Inner join produce dei duplicati. Avremo la Germania tre volte, perché ha tre grandi città. Ma vediamo lo schema con DuplicateWeedout:

Come si può vedere, è stata usata una tabella temporanea con una chiave primaria per evitare di produrre diversi record con 'Germany'.

DuplicateWeedout in azione

Start temporary e End temporary, presenti nell'ultimo diagramma, vengono mostrati nell'output di EXPLAIN:

MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 0.33 * Country.Population and City.Population > 1*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
         type: range
possible_keys: Population,Country
          key: Population
      key_len: 4
          ref: NULL
         rows: 238
        Extra: Using index condition; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.City.Country
         rows: 1
        Extra: Using where; End temporary
2 rows in set (0.00 sec)

Questa query legge 238 righe dalla tabella City e per ognuna di esse svolge una ricerca su chiave primaria nella tabella Country, dove trova altre 238 righe. Il totale è di 476 righe, a cui bisogna aggiungere 238 ricerche nella tabella temporanea (che solitamente sono *poco* costose, in quanto la tabella temporanea è in memoria).

Se si esegue la stessa EXPLAIN in MySQL, si ottiene:

mysql> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 0.33 * Country.Population and City.Population > 1*1000*1000)
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: City
         type: index_subquery
possible_keys: Population,Country
          key: Country
      key_len: 3
          ref: func
         rows: 18
        Extra: Using where
2 rows in set (0.00 sec)

Questo piano legge (239 + 239*18) = 4541 righe, quindi è molto più lento.

Riassunto

• DuplicateWeedout appare come "Start temporary/End temporary" nelle EXPLAIN.
• Questa strategia è in grado di gestire le subquery correlate.
• Ma non può essere usata se la subquery ha una GROUP BY significativa e/o funzioni aggreganti.
• DuplicateWeedout permette all'ottimizzatore di mischiare liberamente le tabelle di una subquery con quelle della SELECT madre.
• Non esiste un flag separato in @@optimizer_switch per la strategia DuplicateWeedout. Essa può essere disabilitata disattivando tutte le ottimizzazioni sulle semi-join, con il comando SET @@optimizer_switch='optimizer_semijoin=off'.

Vedi anche

• Cosa c'è in MariaDB 5.3
• Mappa delle ottimizzazioni delle subquery