WHERE warunek vs LEFT JOIN warunek

Być może nie powinienem publicznie przyznawać się, że popełniłem tak podstawowy błąd w konstruowaniu zapytania SQL, ale po jego odkryciu szczerze się uśmiałem, a skoro śmiech to zdrowie to się nim podzielę.

W uproszczeniu: mam tabelkę z danymi klientów (client) i tabelkę produktów (product), pomiędzy którymi istnieje relacja jeden do wielu. Wyciąganie danych do pokazania jest zrealizowane w Doctrine w następujący sposób:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Dostałem za zadanie zmienić wyświetlanie danych w ten sposób by pokazywać jedynie aktywne produkty. Zrobiłem to tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->andWhere('product.isActive = 1')
    ->setParameter('identifier', $identifier);

Ku memu zdziwieniu w wypadku klienta, który ma tylko jeden nieaktywny produkt wynik zapytania był pusty. No jakże to?! Przecież klient jak najbardziej istnieje, a ponieważ nie ma produktów spełniających kryteria wyszukiwania (isActive = 1) to w miejscu danych produktu powinny być NULLe.

Swój błąd zrozumiałem dopiero, kiedy spojrzałem na generowane przez Doctrine zapytanie SQL. W uproszczeniu wygląda ono tak:

SELECT 
  c.name,
  p.product_type
FROM 
  customer c 
  LEFT JOIN product p ON c.id = p.customer_id 
WHERE 
  c.identifier = '65092900214'
  AND p.is_active = 1

Ponieważ warunek p.is_active = 1 sprawdzamy w części WHERE zapytania to eliminujemy z zapytania wszystkie wiersze, w których on nie jest spełniony. Zatem jeśli klient ma tylko jeden nieaktywny produkt to wynik zapytania będzie pusty.

Oczekiwany efekt (wiersz z NULLami w miejscu danych produktu) uzyskamy przenosząc warunek do części LEFT JOIN zapytania:

SELECT 
  c.name,
  p.product_type
FROM customer c
  LEFT JOIN product p ON c.id = p.customer_id 
  AND p.is_active = 1
WHERE 
  c.identifier = '65092900214'

Ponieważ warunek dotyczy złączenia to jeśli nie zostanie on spełniony to złączenie nie zostanie wykonane, ale dane klienta zostaną zwrócone. Dokładnie tak jak chciałem.

W Doctrine poprawne zapytanie należy zbudować tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product', 'WITH', 'product.isActive = 1')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Klucz kluczem do sukcesu

Mechanizm planowania zapytań w MySQL jest mniej sprytny niż sądziłem. Chciałem wykonać z pozoru łatwe zapytanie: z bazy użytkowników liczącej ok 130 tys. wierszy chciałem wyłuskać osoby mieszkające pod określonymi kodami pocztowymi (ok. 20 tys.). Dodatkowym warunkiem było podanie numeru telefonu, który znajduje się w innej tabelce oraz wyrażenie zgody na marketing, ale to nie jest istotne dla głównego problemu. Napisałem takie zapytanie:

SELECT
	k.imie,
	k.nazwisko,
	k.kod_pocztowy,
	u.numer_telefonu
FROM klient k
INNER JOIN ustawienia u ON k.id = u.klient_id
INNER JOIN tmp_kody t ON k.kod_pocztowy = t.kod_pocztowy
WHERE k.zgoda_na_marketing = 1
ORDER BY k.id

Szukane kody pocztowe wrzuciłem sobie do tabelki, bo przy próbie umieszczenia ich w treści zapytania phpMyAdmin odmówił współpracy, a wklejanie zapytania na zdalnej konsoli trwało ponad 10 minut.

Pierwszą próbę wykonania zapytania przerwałem po kilku minutach. EXPLAIN pokazał, że MySQL nie bardzo chce korzystać z indeksów i najchętniej przeskanowałby całą tabelkę tmp_kody i ustawienia. Próbowałem przepisać zapytanie na kilka sposobów by skłonić MySQL do ułożenia innego planu, ale bezskutecznie. Skutecznym rozwiązaniem okazało się tymczasowe dodanie indeksu na kolumnę kod_pocztowy. Oryginalne zapytanie wykonało się w mgnieniu oka, a EXPLAIN pokazał, że wszystkie etapy planu wykonania zaczęły korzystać z indeksów.

Wniosek na przyszłość: jeśli MySQL sobie nie radzi to można mu pomóc dodając tymczasowy indeks. To może okazać się szybsze niż czekanie na wynik nieoptymalnego zapytania.

Serwer na Debianie: nginx, PHP i bazy danych

Przedstawiam mój przepis szybkie postawienie serwera, na którym działa nginx, PHP i bazy danych. Ja z powodzeniem używam takiej konfiguracji na maszynie deweloperskiej, ale można jej też użyć jako punktu wyjścia do konfiguracji serwera produkcyjnego, jednak w tym wypadku radzę uważnie przeczytać zastrzeżenia na końcu.

W przepisie używam Debiana Squeeze, ale po dodaniu sudo przed większością poleceń będzie go można zastosować do Ubuntu. ;-)

Opisywana konfiguracja spełnia następujące założenia:

  • Na serwerze mają działać nginx, PHP, MySQL, PostgreSQL, MongoDB i memcached.
  • Oprogramowanie powinno być w możliwie aktualnych stabilnych wersjach.
  • Skrypty PHP mają działać z prawami użytkownika.
  • Serwer powinien mieć możliwość uruchamiania skryptów PHP zabezpieczonych ionCube’em.

Czytaj dalej Serwer na Debianie: nginx, PHP i bazy danych