Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: optymalizacja zapytania
Forum PHP.pl > Forum > Bazy danych
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
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
  1. EXPLAIN [twoje_zapytanie]
cojack
select suma1 + suma2 as sumaDodatnia, suma1 - suma2 as sumaUjemna from ( i tu walnij swojego selecta z 2x sum i reszte ) as subSelect;
mrm
Cytat(cojack @ 27.08.2010, 08:02:15 ) *
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
Cytat(Pilsener @ 27.08.2010, 11:59:13 ) *
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
Cytat
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 smile.gif 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
Cytat(cojack @ 28.08.2010, 14:17:29 ) *
Wklej tu te całe zapytanie, sformatowane.


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

  1.  
  2. SELECT
  3. tablica.pole AS alias pola,
  4. tablica2.pole2 AS alias pola2,
  5. tablica3.pole3,
  6. tablica4.pole4,
  7. pole5,
  8. pole6,
  9. pole7,
  10. SUM(
  11. CASE
  12. WHEN (tablica4.pole4 =1) THEN (tablica4.pole1 * pole5)
  13. WHEN (tablica4.pole4 =2 OR tablica4.pole4 =5) THEN (tablica2.pole2 * pole5)
  14. WHEN (tablica4.pole4 =4) THEN (tablica4.pole3 * pole5)
  15. END) AS alias2,
  16. SUM(
  17. CASE
  18. WHEN (pole6 < 13 AND pole6 >0) THEN 50
  19. WHEN pole6 > 12 THEN pole7 * 0.7
  20. END) AS dojazd,
  21. SUM(
  22. CASE
  23. WHEN (tablica4.pole4 =1) THEN (tablica4.pole1 * pole5)
  24. WHEN (tablica4.pole4 =2 OR tablica4.pole4 =5) THEN (tablica2.pole2 * pole5)
  25. WHEN (tablica4.pole4 =4) THEN (tablica4.pole3 * pole5)
  26. END)
  27. +
  28. SUM(
  29. CASE
  30. WHEN (pole6 < 13 AND pole6 >0) THEN 50
  31. WHEN pole6 > 12 THEN pole7 * 0.7
  32. END) AS suma
  33. FROM
  34. tablica1,
  35. tablica2,
  36. tablica3,
  37. tablica4,
  38. tablica5,
  39. tablica6,
  40. tablica7,
  41. tablica8,
  42. tablica9
  43.  
  44.  
  45. WHERE
  46. tablica.pole = tablica.pole
  47. AND
  48. tablica.pole = tablica.pole
  49. AND
  50. tablica.pole = tablica.pole
  51. AND
  52. tablica.pole = tablica.pole
  53. AND
  54. tablica.pole = tablica.pole
  55. AND
  56. tablica.pole = tablica.pole
  57. AND
  58. tablica.pole = tablica.pole
  59. AND
  60. tablica.pole = tablica.pole
  61. AND
  62. tablica.pole = tablica.pole
  63. AND
  64. tablica.pole > 0
  65. AND
  66. tablica.pole >= :wartosc
  67. AND
  68. tablica.pole <= :wartosc
  69.  
  70.  
  71. GROUP BY
  72. tablica.pole
  73.  
  74. ORDER BY
  75. tablica.pole ASC
  76. ');
  77.  
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ż ).
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.