Ottimizzazione Table Pullout

Il Table pullout è un'ottimizzazione per le subquery di tipo semi-join.

L'idea di Table Pullout

A volte, una subquery può essere riscritta come join. Per esempio:

select *
from City 
where City.Country in (select Country.Code
                       from Country 
                       where Country.Population < 100*1000);

Se si sa che può esserci al massimo uno stato (country) per ogni singolo valore di Country.Code (questo è certamente vero se Country ha una chiave primaria o un indice univoco su quella colonna), si può riscrivere la query così:

select City.* 
from 
  City, Country 
where
 City.Country=Country.Code AND Country.Population < 100*1000;

Table pullout in azione

Se si esegue EXPLAIN per la query riportata sopra su MySQL 5.1-5.6 o MariaDB 5.1-5.2, si ottiene il piano seguente:

MySQL [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
| id | select_type        | table   | type            | possible_keys      | key     | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | City    | ALL             | NULL               | NULL    | NULL    | NULL | 4079 | Using where |
|  2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY,Population | PRIMARY | 3       | func |    1 | Using where |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Esso dimostra che l'ottimizzatore intende eseguire una scansione completa della tabella City, e cercare ogni singola città nella tabella Country.

Se si esegue la stessa query su MariaDB 5.3, si ottiene il piano seguente:

MariaDB [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type | table   | type  | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY     | Country | range | PRIMARY,Population | Population | 4       | NULL               |   37 | Using index condition |
|  1 | PRIMARY     | City    | ref   | Country            | Country    | 3       | world.Country.Code |   18 |                       |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
2 rows in set (0.00 sec)

Gli elementi interessanti sono i seguenti:

• Entrambe le tabelle hanno select_type=PRIMARY, eid=1 come se fossero parte di una join.
• La tabella `Country` appare per prima, seguita da `City`.

Se si esegue EXPLAIN EXTENDED; SHOW WARNINGS, si vedrà che la subquery è stata sostituita con una join:

MariaDB [world]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS 
`Name`,`world`.`City`.`Country` AS `Country`,`world`.`City`.`Population` AS 
`Population` 

  
   from `world`.`City` join `world`.`Country` where 


((`world`.`City`.`Country` = `world`.`Country`.`Code`) and (`world`.`Country`.
`Population` < (100 * 1000)))
1 row in set (0.00 sec)

Trasformare la subquery in una join permette di ottimizzarla appunto come una join, il che permette di scegliere tra i due possibili ordini di unione delle tabelle:

1. City -> Country
2. Country -> City

a differenza della vecchia ottimizzazione, che permetteva solo:

1. City->Country

Nell'esempio sopra, grazie a questa scelta si ha un piano di esecuzione migliore. Senza il Table pullout, il piano di esecuzione della subquery consisterebbe nel leggere (4079 + 1*4079)=8158 record. Con il Table pullout, il piano della join permette di leggere (37 + 37 * 18) = 703 righe. Le righe da leggere non sono sempre uguali, ma generalmente leggere un numero di record inferiore di 10 volte è molto più rapido.

Table pullout fact sheet

• Table pullout is possible only in semi-join subqueries.
• Table pullout is based on UNIQUE/PRIMARY key definitions.
• Doing table pullout does not cut off any possible query plans, so MariaDB will always try to pull out as much as possible.
• Table pullout is able to pull individual tables out of subqueries to their parent selects. If all tables in a subquery have been pulled out, the subquery (i.e. its semi-join) is removed completely.
• One common bit of advice for optimizing MySQL has been "If possible, rewrite your subqueries as joins". Table pullout does exactly that, so manual rewrites are no longer necessary.

Controlling table pullout

There is no separate @@optimizer_switch flag for table pullout. Table pullout can be disabled by switching off all semi-join optimizations with SET @@optimizer_switch='semijoin=off' command.