Optymalizacja zapytania, Mam sporo informacji ale nie potrafię sobie z tym poradzić Opcje

[TomASS]

Cześć, mam takie oto zapytanie:

[SQL] pobierz, plaintext 
SELECT 
  DISTINCT T.NumerRef 
FROM 
  m_transporty_miejsca AS TM LEFT JOIN 
  m_transporty AS T ON (T.ID=TM.ID_transport) 
WHERE 
  1 AND 
  (AnulowanyFE2='0' OR DateRealizacji>='2011-06-29') AND 
  1 AND 
  T.Odleglosc>='0' AND 
  T.Odleglosc<='99' AND 
  T.ImportFE='1' AND 
  ( T.ID_przewoznik='72' OR (STATUS='przedstawiony' AND 1) OR (Gielda='1' AND STATUS='gielda' AND 1) ) AND 
  ( Miejsce='akt' OR (STATUS='zaksięgowane w SAP' AND DataDostarczenia IS NULL) ) 
ORDER BY T.ID 
[SQL] pobierz, plaintext 

Tabela m_transporty_miejsca 200 000 rekordów
Tabela m_transporty 100 000 rekordów
raczej małe ilości.

Zapytanie wykonuje się około 0,8s (średnia z 20pomiarów).
Jeśli usunę ostatni warunek w klauzuli WHERE:

[SQL] pobierz, plaintext 
 ( Miejsce='akt' OR (STATUS='zaksięgowane w SAP' AND DataDostarczenia IS NULL) ) 
[SQL] pobierz, plaintext 

a szczególnie

[SQL] pobierz, plaintext 
OR (STATUS='zaksięgowane w SAP' AND DataDostarczenia IS NULL) 
[SQL] pobierz, plaintext 

wówczas czas zapytania spada do 0,03 (średnia z 20pomiarów)

Indexy jakie min. mam pozakładane na tabelę m_transporty to:
ImportFE1: (ImportFE)
ImportFE2: (ImportFE+Miejsce)
ImportFE3: (ImportFE+Status+DataDostarczenia)

EXPLAIN tego zapytania wskazuje, że użyty jest indeks "ImportFE1"

Proszę o pomoc w optymalizacji.

[uupah5]

przydałoby się dodać do wszystkich pól aliasy tabel, skoro już dajesz to do publicznej analizy.
pole status występuje kilkukrotnie z czego ostatni raz jest wzajemnie wykluczający się z przedostatnim, więc może jednak przekonstruować to zapytanie tak, aby pole status występowało samodzielnie i nadrzędnie, czyli coś w stylu:
status = a and (....)
or
status = b and (...)
oczywiście to szkic, bo to Ty te dane rozumiesz, więc przebudowanie warunków zostawiam Tobie.
jeśli jest to wykonalne, to dać potem index na status. btw to musi być varchar? oraz rozumiem struktura jest świadomie zdenormalizowana?

a na marginesie, 0.8s to jest faktycznie problem?

[rollen]

TomASS ma rację spróbuj jakoś przebudować tę część ze STATUS, bo jest całkowicie nieczytelana...

Wskazałeś też, że użyty został indeks ImportFE1: (ImportFE). Nie wiem jakie dane masz w columnie ImportFE, ale wydaje mi się, że to jakiś status z ograniczony do kilku wartości. Taki indeks staje ję mało selektywny. Proponuję go rozszerzyć o T.Odległość.
Czyli ImportFE1: (ImportFE, Odleglosc).
Inne zapytania kożystające z tego indeksu nie ucierpią, a to zapytanie dostanie dodatkowego kopa (o ile są wartości w kolumnie Odleglosc wyższe niż w podanym warunku zakresu[0, 99] )

a na marginesie, 0.8s to jest faktycznie problem?

Przy tak małej ilości danych w bazie to jest wieczność...

[TomASS]

pole status występuje kilkukrotnie z czego ostatni raz jest wzajemnie wykluczający się z przedostatnim

Nie wyklucza się - w obydwu przypadkach jest pole status + OR

więc może jednak przekonstruować to zapytanie tak, aby pole status występowało samodzielnie i nadrzędnie, czyli coś w stylu:
status = a and (....)
or
status = b and (...)

Teraz zrobiłem tak:

[SQL] pobierz, plaintext 
SELECT DISTINCT T.NumerRef, T.DataWyjazdu, T.ID_grupa_transportowa , 
T.Pojazd, T.Datetime_do_kiedy_cena_tradycyjna, T.AnulowanyFE2, 
T.HD, T.IloscTowaruRzeczywista, T.Multistop, T.DataDostarczenia, 
PonaglenieDostarczenia, PonaglenieAwizacji, 
TIMESTAMPDIFF(MINUTE, NOW(), T.DateRealizacji) AS CzasDoKoncaRealizacji, 
T.PotwierdzeniePrzyjeciaZmian, T.IloscTowaru, T.Odleglosc, T.TerminWaznosci, 
T.GodzinaRealizacji, (DataDodaniaNaGielde + INTERVAL TerminWaznosci MINUTE) AS LicytacjaDo, 
T.Gielda, T.ID, T.DateRealizacji, T.STATUS, T.Numer, RodzajCiezarowki, ROUND(Cena,2) AS Cena, 
Waluta 
FROM 
m_transporty_miejsca AS TM LEFT JOIN 
m_transporty AS T ON (T.ID=TM.ID_transport) 
WHERE 
1 AND 
(T.AnulowanyFE2='0' OR T.DateRealizacji>='2011-06-30') AND 
1 AND 
T.Odleglosc>='9' AND 
T.Odleglosc<='102' AND 
T.ImportFE='1' AND 
((T.Miejsce='akt' AND ((T.STATUS='przedstawiony' AND 1)
OR (T.STATUS='gielda' AND T.Gielda='1' AND 1)
OR T.ID_przewoznik='72'))
OR (T.STATUS='zaksięgowane w SAP' AND T.DataDostarczenia IS NULL AND T.ID_przewoznik='72'))
ORDER BY T.ID 
[SQL] pobierz, plaintext 

i dalej nic.
Ze statusem to kłopot taki, że chcę wyswietlać rekordy w różnych statusach, ale przy każdym statusie muszą być spełnione dodakowe (za każdym statusem różne) warunki. Wystarczy, że zajdzie któryś z tych warunków:

1. T.Status='gielda' AND T.Gielda='1' AND 1 AND T.Miejsce='akt
2. T.Status='przedstawiony' AND 1 AND T.Miejsce='akt
3. T.ID_przewoznik='72 AND T.Miejsce='akt (bez różnicy na status)
4. T.Status='zaksięgowane w SAP' AND T.DataDostarczenia IS NULL AND T.ID_przewoznik='72'

jeśli jest to wykonalne, to dać potem index na status. btw to musi być varchar?

Status ma index - tak jest varchar

oraz rozumiem struktura jest świadomie zdenormalizowana?

Przypadek

a na marginesie, 0.8s to jest faktycznie problem?

Bardzo długo 12razy dłużej niż bym chciał

TomASS ma rację spróbuj jakoś przebudować tę część ze STATUS, bo jest całkowicie nieczytelana...

tylko jak ? Moja wyobraźnia chyba tego nie ogarnia już:/

Wskazałeś też, że użyty został indeks ImportFE1: (ImportFE). Nie wiem jakie dane masz w columnie ImportFE, ale wydaje mi się, że to jakiś status z ograniczony do kilku wartości.

dwie możliwości - 0 oraz 1

Taki indeks staje ję mało selektywny. Proponuję go rozszerzyć o T.Odległość.

pole odegłość to jedna z 20 możliwośći

dzięki chłopaki - pomóżcie jeszcze troszkę, może coś się uda.

Ten post edytował TomASS 7.07.2011, 00:00:15

[uupah5]

patrząc tylko w kod, bez danych i na sucho. sprawdź sobie czy nie pominąłem jakiegoś warunku.

[SQL] pobierz, plaintext 
(...)
T.ImportFE='1' AND 
(
(T.STATUS='zaksięgowane w SAP' AND (T.DataDostarczenia IS NULL AND T.ID_przewoznik='72'))
OR 
(T.STATUS='przedstawiony' AND (T.Miejsce='akt'))
OR 
(T.STATUS='gielda' AND (T.Miejsce='akt' AND T.Gielda='1'))
OR
(T.Miejsce='akt' AND T.ID_przewoznik='72')
)
[SQL] pobierz, plaintext 

do tego Status jednak bym znormalizował albo przynajmniej zamienił na small int i oczywiśce zaindeksował.
ta sama uwaga do Miejsce - to też jakiś słownik stanów, zdenormalizowany i na varchar?
Gielda to pole binarne czy też varchar? znasz tylko jeden typ danych?

mam nadzieję, że moja propozycja da jakiś efekt. bez danych i struktury tabel opieram się tylko na algebrze boola;)

ps pytałem się czy czas jest krytyczny, bo nie zawsze jest. przykładowo, mógł być to raport co się generuje w nocy.

[TomASS]

Dziękuję za pomoc - mam teraz zapytanie tak:

[SQL] pobierz, plaintext 
SELECT DISTINCT T.NumerRef 
FROM 
m_transporty_miejsca AS TM LEFT JOIN 
m_transporty AS T ON (T.ID=TM.ID_transport) 
WHERE 
1 AND 
(AnulowanyFE2='0' OR DateRealizacji>='2011-06-30') AND 
1 AND 
T.Odleglosc>='0' AND 
T.Odleglosc<='99' AND 
T.ImportFE='1' AND 
(
(T.STATUS='zaksięgowane w SAP' AND (T.DataDostarczenia IS NULL AND T.ID_przewoznik='72'))
OR
(T.STATUS='przedstawiony' AND (T.Miejsce='akt'))
OR
(T.STATUS='gielda' AND (T.Miejsce='akt' AND T.Gielda='1'))
OR
(T.Miejsce='akt' AND T.ID_przewoznik='72')
)
 ORDER BY T.ID
[SQL] pobierz, plaintext 

I nie zanotowałem, żadnego przyspieszenia
EXPLAIN wskazuje, że używany nadal jest tylko indeks ImportFE1: (ImportFE).
Pole status ma około 30 wartości a jego zmiana będzie b. kłopotliwa - wolałbym tego uniknąć.

Gielda to pole binarne czy też varchar? znasz tylko jeden typ danych?

Gielda - ENUM 0,1,2,3


Ten post edytował TomASS 7.07.2011, 10:44:45

[nospor]

STATUS powinno być ENUM a nie smallint. Wówczas nie trzeba będzie zmieniać zapytań, które zapewne się przewalają przez całą aplikację.

Gielda - ENUM 0,1,2,3

Trochę dziwne używać ENUM dla wartosci numerycznych

[uupah5]

cytat z mauala mysql:
we strongly recommend that you do not use numbers as enumeration values

poza tym, wrzuć w goole "why enum is evil" albo poszukaj na forum, bo chyba już kiedyś zapodawałem linka z art. na ten temat

a co do braku efektu po zmianach - status jest zaindeksowane?

ps piszesz, że chcesz uniknąć przeróbek. obym był złym prorokiem, ale moim zdaniem źle zaprojektowana baza danych da Ci się we znaki jeszcze nie raz

[TomASS]

STATUS powinno być ENUM a nie smallint. Wówczas nie trzeba będzie zmieniać zapytań, które zapewne się przewalają przez całą aplikację.

Trochę dziwne używać ENUM dla wartosci numerycznych

Sugerujesz, że lepiej zmienić na smallint?

[nospor]

Gielda? Mówię jedynie że to jest dziwne To tak jakbym używał większej szklanki do trzymania w niej mniejszej szklanki

[TomASS]

Nic nie dała zmiana pola Status na ENUM

[ano]

Myślę, że zmiana STATUS i miejsce na pola TINYINT (unsigned przechowuje liczby do 255 więc w zupełności powinno wystarczyć do tych celów) rozwiąże Twój problem. O wiele cięższe jest porównanie stringów niż intów i z tym każdy musi się zgodzić.
W ogóle, wydaje mi się, że to, że status jest przechowywany w formie stringa świadczy o tym, że ktoś źle zaprojektował tą bazę. (jeśli dobrze sobie to wyobrażam, to STATUS to nie jest dowolny string, a jeden spośród listy możliwych statusów - więc idealnie int się do tego nadaje.

Co do pola miejsce to zależy co tam przechowujesz, jak działa na tej samej zasadzie jak "status" to też można by było przerobić na inta.

To pociąga ze sobą oczywiście zmiany w kodzie Twojej aplikacji. Jak wcześniej po prostu wyświetlałeś status to tutaj będziesz musiał robić coś w stylu $status[ID_STATUSU_POBRANE_Z_DB]. i $status = array('blablabla','blabla');
ale myślę, że o takich 'oczywistościach' już nie musze wspominać?

[everth]

Abstrahując od powyższych wypowiedzi - zwróciłeś uwagę na fakt że warunek który drastycznie zmienia czas wykonania różni się czymś od pozostałych? Bo jak dla mnie wyróżnia go jedno - klauzula IS(NOT) NULL. Wcześniej wykonujesz LEFT JOIN - złączenie zewnętrzne. Te złączenia nie dają się tak łatwo optymalizować jak złączenia wewnętrzne (INNER JOIN) zwłaszcza że tutaj dokonujesz porównania na wartości NULL (która jest tworzona przez LEFT JOIN). Podejrzewam że z tym warunkiem baza musi przemielić znacznie więcej rekordów niż bez niego - i to pewnie wpływa na czas wykonania. Poeksperymentuj jak to wygląda bez porównań do NULLa (lub spróbuj je jakoś wyeliminować).

[TomASS]

Abstrahując od powyższych wypowiedzi - zwróciłeś uwagę na fakt że warunek który drastycznie zmienia czas wykonania różni się czymś od pozostałych? Bo jak dla mnie wyróżnia go jedno - klauzula IS(NOT) NULL. Wcześniej wykonujesz LEFT JOIN - złączenie zewnętrzne. Te złączenia nie dają się tak łatwo optymalizować jak złączenia wewnętrzne (INNER JOIN) zwłaszcza że tutaj dokonujesz porównania na wartości NULL (która jest tworzona przez LEFT JOIN). Podejrzewam że z tym warunkiem baza musi przemielić znacznie więcej rekordów niż bez niego - i to pewnie wpływa na czas wykonania. Poeksperymentuj jak to wygląda bez porównań do NULLa (lub spróbuj je jakoś wyeliminować).

Dzięki, całkowite usunięcie warunki z IS NULL nic nie dało w przyspieszeniu zapytania

rochę dziwne używać ENUM dla wartosci numerycznych

Czy dla pól, które mogą przyjąć tylko wartości 0/1/2/3 - lepiej jest użyć (small)int czy Enum (0/1/2/3)?
Czy konwersja ENUM->SMALLINT nie sprawi mi kłopotu (utrata danych/kłopoty w zapytaniach?)

dzięki.

Ten post edytował TomASS 10.07.2011, 13:35:53