L'ottimizzazione delle Derived Table con chiave

L'idea

Se una Derived Table non può essere fusa con la SELECT principale, verrà materializzata in una tabella temporanea e la SELECT madre la tratterà come una normale tabella fisica.

Prima di MariaDB 5.3/MySQL 5.6, queste tabelle temporanee non potevano avere indici e l'unico modo per leggere i record era effettuare una scansione completa. A partire dalle suddette versioni, l'ottimizzatore ha la possibilità di creare un indice da usare per le join con le altre tabelle.

Esempio

Si consideri la seguente query: si vogliono trovare gli stati europei in cui più di un milione di persone vive nelle città. Ecco come ottenerli:

select * 
from
   Country, 
   (select 
       sum(City.Population) as urban_population, 
       City.Country 
    from City 
    group by City.Country 
    having 
    urban_population > 1*1000*1000
   ) as cities_in_country
where 
  Country.Code=cities_in_country.Country and Country.Continent='Europe';

L'output di EXPLAIN è il seguente:

+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys     | key       | key_len | ref                | rows | Extra                           |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
|  1 | PRIMARY     | Country    | ref  | PRIMARY,continent | continent | 17      | const              |   60 | Using index condition           |
|  1 | PRIMARY     | <derived2> | ref  | key0              | key0      | 3       | world.Country.Code |   17 |                                 |
|  2 | DERIVED     | City       | ALL  | NULL              | NULL      | NULL    | NULL               | 4079 | Using temporary; Using filesort |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+

Si può notare che:

• si accede alla tabella <derived2> attraverso key0.
• la colonna ref contiene world.Country.Code
• nella query originale l'accesso ref si ottiene con un'eguaglianza: Country.Code=cities_in_country.Country.

Riassunto

• L'idea dell'ottimizzazione "derived table con chiave" è far sì che le Derived Table materializzate abbiano una chiave che viene poi usata per le join con altre tabelle.
• Questa ottimizzazione si applica alle Derived Table che non possono essere fuse alla SELECT principale
  • il che accade quando la Derived Table non soddisfa i requisiti dell'algoritmo merge delle viste
• L'ottimizzazione è ON per default, ma può essere disattivata così:

set optimizer_switch='derived_with_keys=off'

Vedi anche

• Optimizing Subqueries in the FROM Clause nel manuale di MySQL 5.6
• Cosa c'è in MariaDB 5.3
• Mappa delle ottimizzazioni delle subquery