Z tego co widzę w logach bloga, jednym z przewijających się przez Google pytań jest pytanie o wydajność zapytania typu:

SELECT kol1, kol2 FROM tabela WHERE kol3 IN (1,2,3);

Na przykładzie bazy danych `sakila` sprawdźmy jak wygląda plan wykonania takiego SELECTa:

mysql> EXPLAIN SELECT * FROM rental WHERE customer_id IN (546, 67)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 47
Extra: Using where
1 row in set (0.00 sec)

Jak widać po zawartości kolumn type i key, zapytanie takie wykorzystuje indeks. Zapytanie typu ‚range’ zawsze korzysta z indeksu, natomiast wartość w kolumnie key wskazuje który indeks został użyty. Aby uzyskać wynik MySQL sprawdził 47 rekordów. Więcej informacji na temat poszczególnych typów znaleźć można w dokumentacji MySQL.

Tak więc mamy ładne, szybkie zapytanie. Sęk w tym, że często może być tak, że wygodniej byłoby napisać zapytanie w inny sposób:

SELECT * FROM rental WHERE customer_id IN (SELECT customer_id FROM customer WHERE first_name='KELLY');

Z punktu widzenia SQL jest to odpowiednik wcześniejszego zapytania, jako że wynikiem podzapytania są wartości 67 i 546:

mysql> SELECT customer_id FROM customer WHERE first_name='KELLY';
+-------------+
| customer_id |
+-------------+
|          67 |
|         546 |
+-------------+
2 rows in set (0.00 sec)


Jak wygląda plan takiego zapytania? Sprawdźmy:

mysql> EXPLAIN SELECT * FROM rental WHERE customer_id IN (SELECT customer_id FROM customer WHERE first_name='KELLY')\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: rental
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16365
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: customer
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

Niestety, jest gorzej – liczba rekordów do sprawdzenia sięga 16 tysięcy, tworzone jest zależne podzapytanie. Przy pomocy EXPLAIN EXTENDED możemy sprawdzić co się stało:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: rental
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16365
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: customer
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: func
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message:  select `sakila`.`rental`.`rental_id` AS  `rental_id`,`sakila`.`rental`.`rental_date` AS  `rental_date`,`sakila`.`rental`.`inventory_id` AS  `inventory_id`,`sakila`.`rental`.`customer_id` AS  `customer_id`,`sakila`.`rental`.`return_date` AS  `return_date`,`sakila`.`rental`.`staff_id` AS  `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from  `sakila`.`rental` where  <in_optimizer>(`sakila`.`rental`.`customer_id`,<exists>(<primary_index_lookup>(<cache>(`sakila`.`rental`.`customer_id`)  in customer on PRIMARY where ((`sakila`.`customer`.`first_name` =  'KELLY') and (<cache>(`sakila`.`rental`.`customer_id`) =  `sakila`.`customer`.`customer_id`)))))
1 row in set (0.00 sec)

Jak widać, nasze zapytanie zostało przepisane na następujące:

SELECT  * FROM rental WHERE EXISTS (SELECT customer_id FROM customer WHERE  first_name='KELLY' AND rental.customer_id=customer.customer_id);

Niestety, w tym przypadku optimizer nie jest na tyle inteligentny aby po prostu wykonać prostego SELECTa w podzapytaniu a następnie wstawić wynik do listy IN. Tego typu rozwiązanie byłoby prostsze, szybsze i wykorzystywałoby indeksy. Skoro w tym wypadku optimizer zawodzi, trzeba go wyręczyć. Jeśli stajemy przed koniecznością zoptymalizowania zapytania takiego typu, można po prostu rozbić je na dwa – wykonać najpierw podzapytanie, a wyniki które udało się uzyskać trzeba wstawić ręcznie do listy IN(). Drugą opcją jest przepisanie zapytania na następujące:

mysql> EXPLAIN SELECT rental.* FROM rental INNER JOIN customer USING(customer_id) WHERE first_name='KELLY'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 620
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.customer.customer_id
rows: 13
Extra:
2 rows in set (0.00 sec)

Tak przy okazji, okazało się że w tabeli `customer` nie ma indeksu nałożonego na kolumnę `first_name`. Jak widać, trzeba sprawdzać plany wszystkich SELECTów – także podzapytań. Jeśli podzapytania nie są poprawnie poindeksowane, to trudno będzie uzyskać dobrą wydajność całości zapytania.

Podsumowując, sama lista IN() jest miła, szybka i przyjemna (zakładając, że liczba argumentów na liście nie sięga kilkuset i więcej). Problem pojawia się gdy w ramach tej listy chcemy zastosować podzapytanie. Niestety, MySQL w tym elemencie nie jest najinteligentniejszy  i zazwyczaj konieczna jest ingerencja administratora i przepisanie zapytania na inny, bardziej strawny dla MySQL format. Co ważne, to wszystko co powyżej napisałem nie znaczy, aby podzapytań w ogóle nie stosować. Jeśli naturalnym byłoby zastosowania listy IN() z podzapytaniem, to po prostu za każdym razem trzeba dokładnie sprawdzić i przetestować wydajność tego rozwiązania, a także ewentualnych zamienników. Zazwyczaj zamienniki będą szybsze, ale są sytuacje, kiedy to właśnie zastosowanie podzapytania jest najbardziej optymalnym rozwiązaniem z punktu widzenia wydajności.

Benchmark, benchmark, benchmark…