Wedle definicji, SQL jest językiem deklaratywnym. Oznacza to, że programista opisuje, co chce osiągnąć, natomiast sposób osiągnięcia celu pozostawia silnikowi bazy danych. Ta zacna reguła zazwyczaj się sprawdza, dzięki czemu pamiętając o zaledwie kilku regułach można tworzyć zgrabne i szybkie zapytania. Czasami jednak optymalizator podstawia nogę w zupełnie nieoczekiwanym momencie.

Ostatnio miałem następującą sytuację: w pewnej firmie, zajmującej się przekazywaniem dotacji dobroczynnych, trzeba utworzyć raport – listę organizacji, na rzecz których w zadanym okresie czasu dokonano co najmniej jednej donacji.

Zadanie nie wygląda na trudne. Do wykonania raportu wystarczy przeanalizować jedną tabelę myc_charity (listę organizacji – 750 rekordów) i jeden prosty widok  v_eventdonations (dane wykonanych donacji, ok 500 tysięcy rekordów). Widok powstał z normalnego złączenia 4 tabel, nie zawiera żadnych złożonych warunków, funkcji agregujących czy innych takich wynalazków. Defacto, jest tylko „skrótem”, stosowanym dla uniknięcia powtarzających się złączeń w kolejnych zapytaniach.

Pierwsze podejście do zadania wyglądało tak:

select
 CharityID, Name AS CharityName, OrgID
from
 myc_charity
where
CharityID in (
 select distinct ved.CharityID from v_eventdonations ved
 where
   ved.PaymentDate >= '2012-10-1'
   and ved.PaymentDate <= '2012-10-31'
   and (DEStatusID & 1) > 0
);

Rezultat: 235 rekordów. Czas wykonania: 619 sekund. Absurdalny! Tym bardziej absurdalny, że myc_charity.CharityID jest kluczem głównym, a samo podzapytanie wykonuje się w 0.81 sekundy.

Zwykle podejrzenie pada na skorelowanie podzapytania, ale w tym wypadku nie byłem w stanie znaleźć korelacji. Co prawda czas wykonania wskazywał na jej istnienie: 750 * 0.81 = 607 sekund, czyli niemal dokładnie tyle, ile zajmowało wygenerowanie raportu. Polecenie EXPLAIN również twierdziło, że zapytanie jest skorelowane:

mysql> explain select CharityID, Name AS CharityName, OrgID from myc_charity whe
re CharityID in (select distinct ved.CharityID from v_eventdonations ved where v
ed.PaymentDate >= '2012-10-1'
-> and ved.PaymentDate <= '2012-10-31'
-> and (DEStatusID & 1) > 0
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: myc_charity
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1228
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: utc
type: ref
possible_keys: PRIMARY,UserEventID_2,UserEventID,CharityID
key: CharityID
key_len: 4
ref: func
rows: 27
Extra: Using where; Using temporary
[.........]

Dopiero po wykonaniu EXPLAIN wykonanie SHOW WARNINGS pokazuje perfidię optymalizatora:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select
  `mycharity_live`.`myc_charity`.`CharityID` AS `CharityID`,
  `mycharity_live`.`myc_charity`.`Name` AS `CharityName`,
  `mycharity_live`.`myc_charity`.`OrgID` AS `OrgID`
from
  `mycharity_live`.`myc_charity`
where <in_optimizer>(
  `mycharity_live`.`myc_charity`.`CharityID`,
  <exists>(
    select distinct 1
    from `mycharity_live`.`myc_userevents` `ev`
      join `mycharity_live`.`myc_userevent_to_charity` `utc`
      join `mycharity_live`.`myc_donationevent_to_charity` `dtc`
      join `mycharity_live`.`myc_donationevent` `de`
    where (
      (`mycharity_live`.`ev`.`UserEventID` = `mycharity_live`.`utc`.`UserEventID`)
      and (`mycharity_live`.`dtc`.`UserEventCharityID` = `mycharity_live`.`utc`.`UserEventCharityID`)
      and (`mycharity_live`.`de`.`DonationEventID` = `mycharity_live`.`dtc`.`DonationEventID`)
      and (`mycharity_live`.`de`.`PaymentDate` >= '2012-10-1')
      and (`mycharity_live`.`de`.`PaymentDate` <= '2012-10-31')
      and (
        (`mycharity_live`.`de`.`DEStatusID` & 1) > 0)
        and (<cache>(`mycharity_live`.`myc_charity`.`CharityID`) = `mycharity_live`.`utc`.`CharityID`
      )
)))
1 row in set (0.00 sec)

Cóż się tu wydarzyło? Ano, optymalizator „zoptymalizował” zapytanie. W tym niestety przypadku zamienił operator IN na operator EXISTS, a zapytanie wewnętrzne skorelował (ostatni warunek) z zapytaniem zewnętrznym. Efektem była fatalna wydajność tego, wydawałoby się, prostego zapytania.

Jak wybrnąć z tego problemu? Trzeba się odwołać do jednej z podstawowych wskazówek SQL-a: w miarę możliwości przesuwaj warunki z WHERE do złączeń.

select
    distinct c.CharityID, c.`Name` AS CharityName, c.OrgID
from
    myc_charity c
    left join v_eventdonations ved on ved.CharityID = c.CharityID
        and ved.PaymentDate >= '2012-10-1'
        and ved.PaymentDate <= '2012-10-31'
        and (ved.DEStatusID & 1) > 0
where ved.CharityID is not null;

Rezultaty: identyczne z pierwszym zapytaniem. Czas wykonania: 0.25 sekundy. Przyrost wydajności: 2500 razy