Oblicza MySQL nie są do końca znane przy tworzeniu aplikacji, a problemy optymalizacyjne stają się nie lada problemem przy funkcjonowaniu wersji produkcyjnej projektu. Nie sposób przewidzieć wszystkich możliwości użycia pól, założenia zarówno wspólnych, jak i pojedynczych indeksów posiadających zakładaną przez nas moc i zajętą pamięć na dysku.
Ostatnimi czasy budowałem dość skomplikowany projekt, jeżeli chodzi o złożoność zapytań i wykonywanych przez nie operacje matematyczne. Pomimo tego, że aplikacja była doskonale przemyślana, a struktury bazy danych perfekcyjnie jej podporządkowane, gdzieś tkwił problem, bowiem jedno z zapytań generowało pozornie prosty (wizualnie) rezultat, baza reagowała na zapytanie dopiero po 2.5 sekundy dla 30k+ rekordów. Patrząc na strukturę kluczy i zapytania, zwłaszcza, że pola, na których operowałem były różnego rodzaju liczbami i datami zacząłem się poważnie martwić i rozkładać zapytanie na czynniki pierwsze, kończąc na warunkach. Wyobraźcie sobie moje zdziwienie, gdy doszedłem do tego, że całe obciążenie (ponad 2.3 sekundy) generował warunek:
WHERE DATE(ticket_date) >= " ... "
Gdzie ticket_date to pole typu DATETIME. Od razu doszedłem do wniosku, że w parze idzie złe przygotowanie danych przez PHP, a angażowana jest w to wszystko baza, na której forsuje się użycie funkcji DATE(). Przynajmniej dla 30k+ rekordów zindeksowanego pola. Prosty zabieg zamiany jednej linijki kodu na drugą przyniósł porządane efekty.
$aTerms[] = 'DATE(ticket_date) >= "' . $sDate . '"'
$aTerms[] = 'ticket_date >= "' . date('Y-m-d H:i:s', strtotime($sDate)) . '"'
Budując aplikację zwracam szczególną uwagę na strukturę bazy, indeksowanie pól, rysuję diagramy przewidujące wykorzystanie danych pod różne zapytania, ale… tak banalny błąd przy przeanalizowanej aplikacji rozłożył mnie na łopatki. Z drugiej strony, zapomniałem o jednej bardzo ważnej rzeczy: maksymalnym odciążeniu bazy danych przy preparowaniu argumentów warunków, skoro warunki te mogą być w odpowiedni i przede wszystkim szybki sposób spreparowane na poziomie modelu (abstrakcyjnie rzecz ujmując, pozbywam się pojęcia PHP), który przygotują zapytanie tylko do wykonania operacji na surowych danych, bez konieczności ich ewentualnego przeliczania. Oczywiście nie zawsze taki efekt da się uzyskać, ale należy to maksymalnie optymalizować.
Jedno jest wiadome: przeliczanie DATE() dla rekordów w warunku jest nieoptymalne dla pola DATETIME.
Ameryki nie odkryłeś. Każdy tekst o wydajności mysqla Ci powie że nie wolno nadużywać funkcji, i fix tego robi się w dokładnie taki sposób jak podałeś.
I mam pytanie, czy rozkładając zapytanie na czynniki pierwsze wpadłeś na pomysł aby użyć EXPLAIN? który tą podpowiedź dał Ci odrazu?
Oczywiście, że nie odkryłem, nic nowego. Natomiast chcę zwrócić na ten problem szczególną uwagę, jest to prawda ogólnie znana, ale nie zawsze się o niej pamięta – takie uwypuklenie, jak banalna rzecz może popsuć najbardziej rozpracowany projekt. Chwila nieuwagi i zapytanie nieoptymalne. Komuś się przyda.
Zawsze używam EXPLAIN, ale i tak rozbierałem zapytanie, żeby widzieć różnice, jakie serwuje wynik EXPLAIN dla kolejnych zapytań.
Trzeba jeszcze zwrócić szczególną uwagę na format daty. Zdarza się, że w bazie ustawi się format w stylu php-owego Y-m-d H:i:s a w skrypcie pobiera samo Y-m-d. Wtedy to, po użyciu
date('Y-m-d H:i:s', strtotime($sDate))wyniki porównań mogą nie zwracać należytych wyników w niektórych przypadkach.
nie to żebym się czepiał, ale klejenie SQL-a z palca to mało edukacyjny sposób prezentowania tego tematu
A jeżeli chodzi o metitum sprawy to
$aTerms[] = 'ticket_date >= DATE(’ . $sDate . ’)’
też działa sensownie
@prachwal: Twój kod nie zadziała z jednej prostej przyczyny. Raz, że pole do którego przyrównujesz datę to DATETIME (podajesz DATE), a dwa, chyba nasze wnioski dotyczące odpowiedniego przygotowania danych Ci się zbytnio nie przydały, skoro po raz kolejny angażujesz bazę w coś, co może być zrobione wcześniej.
$aTerms[] = „ticket_date >= CAST('” . $sDate . „’ AS DATETIME)”;
Wydaje się być rozsądniejszym rozwiązaniem.
@tiraeth: dokładnie, trzeba pamiętać gdzie rozłożyć ciężar. Athlan niepotrzebnie operację date robił dla 30 tys. rekordów. Ew. może warunek trzeba było dać na końcu? Tak aby inne warunki zmniejszyły nam ilość rekordów. Pamiętajmy to też ważne! (kolejność warunków)