Ottimizzazione da EXISTS a IN

MySQL (perfino MySQL 5.6) prevede una sola possibile strategia per eseguire le subquery EXISTS. La strategia è sostanzialmente un'esecuzione diretta, "naive", senza riscritture.

MariaDB 5.3 ha introdotto una ricca gamma di ottimizzazioni per le subquery IN. Ora, è interessante trasformare una clausola EXISTS in una IN, per poter applicare queste ottimizzazioni.

Una EXISTS viene convertita in una IN in due casi:

1. Subquery EXISTS correlate in modo triviale
2. Semi-join EXISTS

Ma analizziamole nei dettagli.

Subquery EXISTS correlate in modo triviale

Spesso la subquery EXISTS è correlata, ma la correlazione è triviale. La subquery è del tipo:

EXISTS (SELECT ...  FROM ... WHERE col_esterna= col_interna AND where_interna)

Dove "outer_col" è l'unica menzione alla subquery che si trova nei campi esterni. In questo caso, la subquery può essere riscritta come una IN non correlata:

outer_col IN (SELECT col_interna FROM ... WHERE where_interna)

(E' necessaria una cura particolare per i valori NULL, si eda sotto). MariaDB, per le subquery IN non correlate, può scegliere tra due strategie di esecuzione, in base al loro costo:

• da IN- a EXISTS (essenzialmente, riconvertirla in una EXISTS)
• Materialization

In pratica, convertire una EXISTS correlata in modo triviale in una IN dà all'ottimizzatore la possibilità di utilizzare la Strategia di materializzazione.

Attualmente la conversione EXISTS->IN è possibile solo per le subquery che si trovano al livello più alto della clausola WHERE, o che si trovano in una operazione NOT che sia direttamente al livello più alto della clausola WHERE.

Semi-join EXISTS

Se una subquery EXISTS è una parte di AND nella clausola WHERE:

SELECT ... FROM tab_esterne WHERE EXISTS (SELECT ...) AND ...

soddisfa la proprietà principale delle subquery semi-join:

con le subquery semi-join, ci interessano solo i record di outer_tables che trovano corrispondenze nella subquery

L'ottimizzatore delle Semi-join prevede una ricca gamma di strategie di esecuzione per le subquery, siano essere correlate o non correlate. Una di queste è la strategia FirstMatch, che equivale al modo in cui sono eseguite le subquery EXISTS, perciò non si esclude alcuna possibilità quando si converte una EXISTS in una semi-join.

In teoria, ha senso convertire tutti i tipi di subquery EXISTS: sia quelle correlate sia quelle non correlate, indipendentemente dal fatto che abbiano o meno un confronto inner=outer.

In pratica, una subquery viene convertita solo se ha un confronto di uguaglianza inner=outer. Vengono convertite sia le correlate sia le non correlate.

Gestire i valori NULL

TODO: riformulare:

• IN ha una semantica complicata per NULL. NOT EXISTS no.
• da-EXISTS-a-IN aggiunge IS NOT NULL prima del predicato della subquery predicate, quando necessario

Controllo

E' possibile controllare l'ottimizzazione con il flag exists_to_in di @@optimizer_switch. Attualmente, è OFF per default.

Limitazioni

Da-EXISTS-a-IN non gestisce:

• le subquery che hanno GROUP BY, funzioni aggreganti o HAVING
• subquery che sono UNION
• un certo numero di casi limite folli