Tabele tymczasowe kojarzą się głównie z przeciętną, żeby nie powiedzieć kiepską wydajnością zapytań. MySQL tworzy takie tabele w momencie gdy konieczne jest np. posortowanie wyników w sposób wykluczający użycie indeksów, jeśli wyniki są grupowane bez użycia indeksów, jeśli wykorzystywane są podzapytania i w wielu innych przypadkach. Tabele tymczasowe są tworzone przez MySQL automatycznie, istnieje też możliwość samodzielnego utworzenia takiej tabeli przez użytkownika. O tabelach generowanych przez MySQL, o ich cechach i o tym kiedy i dlaczego są tworzone pisałem już wcześniej. Dziś parę słów o tabelach “na żądanie”.

Jedna uwaga, której zabrakło w poprzednim poście. W przypadku automatycznie tworzonych tabel nie ma możliwości sterowania indeksami. MySQL samodzielnie decyduje o tym, jakie indeksy są tworzone (o ile w ogóle jakiekolwiek). Konkretnie, tworzone są indeksy na kolumny wykorzystywane w funkcji agregacyjnej, w GROUP BY, DISTINCT. Tego typu indeksy są tworzone zawsze, nie zależnie czy tabela tymczasowa utworzona została w pamięci czy na dysku.

Ok, teraz przejdźmy do dzisiejszego tematu. Drugim rodzajem tabel tymczasowych są tabele tworzone przez użytkownika. Tworzone są przy pomocy zapytania typu:

CREATE TEMPORARY TABLE tab (a INT);

Tabele takie tworzone są na domyślnym silniku, można także samodzielnie zdefiniować jaki silnik chcemy wykorzystać:

mysql> CREATE TEMPORARY TABLE tab (a INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE tab1 (a INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE tab2 (a INT) ENGINE=MEMORY;
Query OK, 0 rows affected (0.00 sec)

Ile i jakie tabele tymczasowe zostały utworzone w danej sesji można sprawdzić zapytaniem (przynajmniej w MySQL 5.5):

mysql> SELECT * FROM information_schema.TEMPORARY_TABLES\G
*************************** 1. row ***************************
SESSION_ID: 5849
TABLE_SCHEMA: test
TABLE_NAME: tab2
ENGINE: MEMORY
NAME: #sql7383_16d9_2
TABLE_ROWS: 0
AVG_ROW_LENGTH: 8
DATA_LENGTH: 0
INDEX_LENGTH: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
*************************** 2. row ***************************
SESSION_ID: 5849
TABLE_SCHEMA: test
TABLE_NAME: tab1
ENGINE: MyISAM
NAME: #sql7383_16d9_1
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
INDEX_LENGTH: 1024
CREATE_TIME: 2011-08-08 08:42:50
UPDATE_TIME: 2011-08-08 08:42:50
*************************** 3. row ***************************
SESSION_ID: 5849
TABLE_SCHEMA: test
TABLE_NAME: tab
ENGINE: InnoDB
NAME: #sql7383_16d9_0
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
INDEX_LENGTH: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
3 rows in set (0.00 sec)

Jak widać, tabela `tab` działa w oparciu o InnoDB – domyślny silnik dla MySQL 5.5.
Tabele tymczasowe tworzone przez użytkownika funkcjonują tylko przez czas trwania sesji. Po zerwaniu połączenia są kasowane.

Ważną cechą tego typu tabel jest to, że istnieje możliwość definiowania dla nich dowolnych indeksów – tak samo jak dla każdej innej “normalnej” tabeli.

mysql> CREATE TEMPORARY TABLE tab_idx (a INT, b VARCHAR(100), c INT);
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tab_idx ADD INDEX idx_a (a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tab_idx ADD INDEX idx_a_b (a, b(30));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tab_idx ADD INDEX idx_b_c (b, c);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM tab_idx;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|  Table   | Non_unique | Key_name | Seq_in_index | Column_name |  Collation | Cardinality | Sub_part | Packed | Null | Index_type |  Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|  tab_idx |          1 | idx_a    |            1 | a           | A          |           0 |     NULL | NULL   | YES  | BTREE      |         |                |
|  tab_idx |          1 | idx_a_b  |            1 | a           | A          |           0 |     NULL | NULL   | YES  | BTREE      |         |                |
|  tab_idx |          1 | idx_a_b  |            2 | b           | A          |           0 |       30 | NULL   | YES  | BTREE      |         |                |
|  tab_idx |          1 | idx_b_c  |            1 | b           | A          |           0 |     NULL | NULL   | YES  | BTREE      |         |                |
|  tab_idx |          1 | idx_b_c  |            2 | c           | A          |           0 |     NULL | NULL   | YES  | BTREE      |         |                |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

Ta cecha bywa szczególnie przydatna. Są pewne zapytania, w których nie ma możliwości aby uniknąć utworzenia tabeli tymczasowej – choćby zapytania z JOINem, w których wyniki chcemy sortować po kolumnach należących do obydwu tabel. W takiej sytuacji może się okazać, że szybsze będzie samodzielne utworzenie tabeli tymczasowej, wrzucenie do niej danych z JOINa, dodanie odpowiednich indeksów i następnie wyciągnięcie danych przy pomocy indeksów właśnie.

Przykładowo, dla danych z bazy Sakila, mogłoby to wyglądać następująco:

mysql>  EXPLAIN SELECT first_name, rating FROM actor LEFT JOIN film_actor  USING(actor_id) LEFT JOIN film USING(film_id) WHERE title='ELEPHANT  TROJAN' ORDER BY first_name, rating\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ref
possible_keys: PRIMARY,idx_title
key: idx_title
key_len: 767
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra:
3 rows in set (0,00 sec)

JOIN trochę bez sensu, ale nie o sens tu chodzi tylko o zasadę. Sortujemy po kolumnach `first_name` i `rating` – należą do dwóch tabel, konieczne jest utworzenie tabeli tymczasowej. Wyniki są sortowane przy pomocy algorytmu filesort. Możemy to zrobić też tak:

mysql> CREATE TEMPORARY TABLE temp (first_name VARCHAR(20), rating VARCHAR(6));
mysql> ALTER TABLE temp ADD INDEX idx_first_name_rating (first_name, rating);
mysql>  INSERT INTO temp SELECT first_name, rating FROM actor LEFT JOIN  film_actor USING(actor_id) LEFT JOIN film USING(film_id) WHERE  title='ELEPHANT TROJAN';

W tym momencie EXPLAIN wygląda tak:

mysql> EXPLAIN SELECT * FROM temp ORDER BY first_name, rating\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: temp
type: index
possible_keys: NULL
key: idx_first_name_rating
key_len: 32
ref: NULL
rows: 5
Extra: Using index
1 row in set (0,00 sec)

Wyniki są sortowane przy pomocy indeksu. Co prawda tabelę tymczasową musieliśmy utworzyć, ale już sortowanie jest realizowane w wydajniejszy sposób. W przypadku pięciu rekordów sensu robić tego nie ma, ale w przypadku pięciuset tysięcy przeprowadzenie takiej kombinacji może okazać się dobrym pomysłem.

Da się to też doskonale wykorzystać w sytuacji gdy aplikacja nie koniecznie musi dostawać najświeższe i najbardziej aktualne dane. Jeśli udałoby się taką tabelę tymczasową użyć np. do obsłużenia kilkunastu albo i więcej zapytań (np. tworzymy zestawienie komentarzy na stronie i odświeżamy je co kilka minut – taki cache po stronie bazy danych – przez ten czas wyniki zamiast być generowane na bieżąco, są serwowane z poindeksowanej tabeli tymczasowej), to jest bardzo prawdopodobne że koszty tworzenia tabeli i indeksów zwrócą się z nawiązką.