W poprzednim poście analizowałem zapytanie typu SELECT DISTINCT i pokazywałem jak można uniknąć tworzenia tablicy tymczasowej. Jak sytuacja wygląda, gdy wykonywany jest SELECT DISTINCT na JOINie dwóch tabel? Niestety, tu już sytuacja jest gorsza. Tak jak w przypadku zapytania na pojedynczej tabeli, tworzona jest tabela tymczasowa.

mysql> EXPLAIN SELECT SQL_NO_CACHE DISTINCT text1 FROM tab1 LEFT OUTER JOIN tab2 USING(id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.tab1.id
rows: 1
Extra:
2 rows in set (0.00 sec)

Tym razem, niestety, zastosowanie indeksów nie pomaga. Co można zrobić?

Zlikwidować agregację i usunąć DISTINCT.

mysql> EXPLAIN SELECT SQL_NO_CACHE text1 FROM tab1 LEFT OUTER JOIN tab2 USING(id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.tab1.id
rows: 1
Extra:
2 rows in set (0.00 sec)

Agregowanie wyników przy pomocy DISTINCT zapewnia to, że wszystkie wyniki będą unikalne – po jego usunięciu może się okazać, że część wyników będzie się powtarzać:

mysql> SELECT DISTINCT SQL_NO_CACHE text1 FROM tab2 LEFT OUTER JOIN tab3 USING(id);
+-------+
| text1 |
+-------+
| w     |
| e     |
| f     |
+-------+
3 rows in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE text1 FROM tab2 LEFT OUTER JOIN tab3 USING(id);
+-------+
| text1 |
+-------+
| w     |
| e     |
| e     |
| f     |
| e     |
+-------+
5 rows in set (0.00 sec)

To, jakie dane wrzucane są do tabeli (czy są unikalne, czy też nie), zależy oczywiście od programisty piszącego aplikację. Można napisać aplikację tak, żeby pilnowała unikalności danych, ale jako że błędy się zdarzają, dobrze też jest wykorzystać mechanizmy udostępniane przez MySQL. Konkretnie chodzi tu o unikalne indeksy – ‚UNIQUE INDEX’. Zastosowanie takiego indeksu na kolumnie gwarantuje, że rekordy z tej kolumny będą unikalne. Nałóżmy taki indeks na kolumnę `text1` w tabeli `tab2`:

mysql> ALTER TABLE tab2 ADD UNIQUE INDEX idx_uniq_text1 (text1);
ERROR 1062 (23000): Powtórzone wystąpienie 'e' dla klucza 'idx_uniq_text1'

Nałożenie klucza się nie powiodło, gdyż wartość ‚e’ występuje w tej kolumnie wielokrotnie – widać to było na wcześniejszym SELECTcie. Skasujmy więc nadmiarowe dane i nałóżmy indeks:

mysql> SELECT * FROM tab2;
+----+-----+-------+
| id | id2 | text1 |
+----+-----+-------+
|  2 |   2 | w     |
|  3 |   3 | e     |
|  5 |   4 | e     |
|  6 |   5 | f     |
|  7 |   4 | e     |
+----+-----+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM tab2 WHERE id=7;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM tab2 WHERE id=5;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE tab2 ADD UNIQUE INDEX idx_uniq_text1 (text1);
Query OK, 3 rows affected (0.02 sec)
Rekordów: 3  Duplikatów: 0  Ostrzeżeń: 0

Indeks nałożył się poprawnie. Sprawdźmy, czy faktycznie gwarantuje on unikalność kolumny na wypadek niewłaściwego INSERTa:

mysql> INSERT INTO tab2 (id2, text1) VALUES (1,'f');
ERROR 1062 (23000): Powtórzone wystąpienie 'f' dla klucza 'idx_uniq_text1'

Oczywiście, utrzymywanie indeksu także wymaga pewnej ilości pracy dla serwera MySQL – wszelkie dodawanie, usuwanie czy modyfikowanie danych muszą się przekładać na takie same operacje na indeksie. Są to dodatkowe operacje dyskowe konieczne do wykonania. Może się okazać, że w jakiejś sytuacji  zastosowanie tego typu rozwiązania nie będzie się opłacało – utworzenie unikalnego indeksu będzie zbyt kosztowne, bądź po prostu niemożliwe do wykonania. Niemniej jednak, dobrze pamiętać że istnieje tego typu możliwość. Czasami zastosowanie takiego indeksu i zrezygnowanie z DISTINCT znacznie przyspiesza działanie zapytania.

Pamiętamy, że wszelkie modyfikacje zapytań testujemy wcześniej na aktualnych danych z aktualnej kopii zapasowej i w środowisku jak najbardziej przypominającym produkcyjne. To, że zastosowanie danego rozwiązania pomogło w jednym przypadku, nie znaczy że nie pogorszy sprawy w innym. Wszystkie zmiany testujemy.