optymalizacja zapytania Opcje

[mrm]

Witam, chciałbym się dowiedzieć w jaki sposób można zoptymalizować następujący typ zapytania:

SELECT
* pola które potrzenuje *,
SUM(
CASE
WHEN (warunek1) THEN (wartosc1 * wartosc2 )
WHEN (warunek2 OR warunek3) THEN (wartosc3 * wartosc4 )
itd
END) as suma1,
SUM(
CASE
WHEN (warunek1) THEN (wartosc5 * wartosc6 )
WHEN (warunek2 OR warunek3) THEN (wartosc7 * wartosc8 )
END) as suma2,
----------------------------obszar do zoptymalizowania , czyli sumy które były liczone wyżej są wykonywane jeszcze raz
SUM(
CASE
WHEN (warunek1) THEN (wartosc1 * wartosc2 )
WHEN (warunek2 OR warunek3) THEN (wartosc3 * wartosc4 )
itd
END) +
SUM(
CASE
WHEN (warunek1) THEN (wartosc5 * wartosc6 )
WHEN (warunek2 OR warunek3) THEN (wartosc7 * wartosc8 )
END) ,

SUM(
CASE
WHEN (warunek1) THEN (wartosc1 * wartosc2 )
WHEN (warunek2 OR warunek3) THEN (wartosc3 * wartosc4 )
itd
END) -
SUM(
CASE
WHEN (warunek1) THEN (wartosc5 * wartosc6 )
WHEN (warunek2 OR warunek3) THEN (wartosc7 * wartosc8 )
END) ,
.......................

----------------------------obszar do zoptymalizowania
FROM
...
WHERE
...
GROUP BY
...
ORDER BY
...

chodzi mi głównie o obszar zaznaczony do optymalizacji, czyli najlepiej aby wykonać operacje typu suma1 + suma2, suma1 - suma2
silnik bazy danych z jakiego korzystam to mysql

Ten post edytował mrm 27.08.2010, 08:04:20

[wookieb]

Użyj zmiennych definiowanych przez użytkownika
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Jeżeli to nie pomoże, pokaż całe zapytanie a także wynik polecenia

[SQL] pobierz, plaintext 
EXPLAIN [twoje_zapytanie]
[SQL] pobierz, plaintext 

[cojack]

select suma1 + suma2 as sumaDodatnia, suma1 - suma2 as sumaUjemna from ( i tu walnij swojego selecta z 2x sum i reszte ) as subSelect;

[mrm]

select suma1 + suma2 as sumaDodatnia, suma1 - suma2 as sumaUjemna from ( i tu walnij swojego selecta z 2x sum i reszte ) as subSelect;

hm...dziwna sprawa takie coś wykonuje się dłużej niż moja wcześniejsza wersja

[Pilsener]

A potrzebujesz tych wszystkich danych? Jeśli tylko część rekordów jest używana to nie ma sensu przeliczać wszystkich by wyciągać niektóre, lepiej to w PHP zrobić. No i przecież to prosty select z jednej tabeli, nie ma tu co optymalizować, na początek zmierzyłbym samo zapytanie bez tych ifów i porównał czasy, potem kwestia indeksów, ale na 90% problem leży w złym modelu danych, baza jest po to by trzymać w niej dane a nie dokonywać online bardzo dużej liczby skomplikowanych obliczeń.

[mrm]

A potrzebujesz tych wszystkich danych? Jeśli tylko część rekordów jest używana to nie ma sensu przeliczać wszystkich by wyciągać niektóre, lepiej to w PHP zrobić. No i przecież to prosty select z jednej tabeli, nie ma tu co optymalizować, na początek zmierzyłbym samo zapytanie bez tych ifów i porównał czasy, potem kwestia indeksów, ale na 90% problem leży w złym modelu danych, baza jest po to by trzymać w niej dane a nie dokonywać online bardzo dużej liczby skomplikowanych obliczeń.

chciałbym aby były wszystkie dane, ułatwia mi to sprawę, nie wiem czy też php szybciej to przetworzy,
select nie jest z jednej tabeli, jest z 7

[Pilsener]

select nie jest z jednej tabeli, jest z 7

- no to zmierzyć czasy z tymi obliczeniami i bez nich, potem brać pod nóż każdego joina po kolei, jeśli się okaże, że obliczenia, to trzeba będzie pewnie zrobić jakiś bufor, cache, tabelę pośrednią, coś jak liczba postów w tabeli temats etc. Jeśli winne poszczególne tabele to zrobić explain i sprawdzić je po kolei, być może nie ma gdzieś indeksu etc. Kolejność dołączania tabel wbrew pozorom też ma duże znaczenie Jeśli mamy za dużo dużych tabel i tu jest problem, to znów trzeba będzie pomajstrować przy modelu danych by nieco utrudnić edycję/dodawanie a przyśpieszyć odczyt.

[cojack]

Wklej tu te całe zapytanie, sformatowane.

[mrm]

Wklej tu te całe zapytanie, sformatowane.

poniżej schemat całego zapytanie, musiałem pozmieniać nazwy pół i tablic

[SQL] pobierz, plaintext 
 
    SELECT 
                tablica.pole AS alias pola, 
                tablica2.pole2 AS alias pola2, 
                tablica3.pole3, 
                tablica4.pole4,
                pole5, 
                pole6, 
                pole7,
                SUM(
                    CASE
                    WHEN (tablica4.pole4 =1) THEN (tablica4.pole1 * pole5)
                    WHEN (tablica4.pole4 =2 OR tablica4.pole4 =5) THEN (tablica2.pole2 * pole5)
                    WHEN (tablica4.pole4 =4) THEN (tablica4.pole3 * pole5)
                    END) AS alias2,
                SUM(
                    CASE
                    WHEN (pole6 < 13 AND pole6 >0) THEN 50
                    WHEN pole6 > 12 THEN pole7 * 0.7
                    END) AS dojazd,
                SUM(
                    CASE
                    WHEN (tablica4.pole4 =1) THEN (tablica4.pole1 * pole5)
                    WHEN (tablica4.pole4 =2 OR tablica4.pole4 =5) THEN (tablica2.pole2 * pole5)
                    WHEN (tablica4.pole4 =4) THEN (tablica4.pole3 * pole5)
                    END)
                +
                SUM(
                    CASE
                    WHEN (pole6 < 13 AND pole6 >0) THEN 50
                    WHEN pole6 > 12 THEN pole7 * 0.7
                    END) AS suma
         FROM 
                tablica1, 
                tablica2, 
                tablica3, 
                tablica4, 
                tablica5, 
                tablica6, 
                tablica7, 
                tablica8, 
                tablica9
 
 
         WHERE
                tablica.pole = tablica.pole
                AND
                tablica.pole  = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole = tablica.pole
                AND
                tablica.pole  > 0
                AND
                tablica.pole >= :wartosc
                AND
                tablica.pole  <= :wartosc
 
 
         GROUP BY 
                tablica.pole
 
         ORDER BY
                tablica.pole ASC
         ');
 
[SQL] pobierz, plaintext 

[cojack]

I patrz co ja Ci mówiłem, wstaw to do FROM wszystko i teraz usuń pole suma i licz go w SELECT, coś takiego:

SELECT
*,
alias + dojazd as suma
FROM
(
... Twoje zapytanie bez suma ...
) as "subQuery";

Rozumiem że tam w where nie chciało Ci się zmieniać nazw, także jak masz tak wymagające zapytanie to nie masz co się martwić, czasami czegoś się nie da inaczej zrobić i zajmuje trochę czasu. Pomyśl też o indeksach o ile nie masz ich jeszcze pozakladanych ( o tych warunkowych też ).