Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

2 Stron V   1 2 >  
 Reply to this topicStart new topic
> [PHP][MYSQL], Optymalizacja zapytania
Kpt
post
Post #1





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

Witam. Mam bardzo rozlazle zapytanie (duzo laczenia tabel) w klauzuli whete tabela1.id = tabela2.id itd itd... W bazie jest ok 3 tys pozycji a bedzie wiecej... Wyszukiwanie IMO trwa zdecydowanie za dlugo.Zastanawiam sie jak to zrobic, aby skrocic czas poszukiwania. W teorii przyjalem, ze moglbym laczyc np dwie tabele szukajac konkretnego pola i dopiero po odszukaniu jakis wynikow wykonywac ardziej zzlozone zapytanie w celu pozyskania wszystkich danych i wyswietlenia ich na stronie.
Go to the top of the page
+Quote Post
wookieb
post
Post #2





Grupa: Moderatorzy
Postów: 8 989
Pomógł: 1550
Dołączył: 8.08.2008
Skąd: Słupsk/Gdańsk
Wiesz nie zaszkodziłoby pokazac tego zapytania a NAWET struktury tabeli.
Go to the top of the page
+Quote Post
trike
post
Post #3





Grupa: Zarejestrowani
Postów: 82
Pomógł: 9
Dołączył: 9.01.2008

Ostrzeżenie: (0%)
-----

nie wiem czy to jest najlepszy sposob, ale wlasnie robie cos podobnego, mam baze ktora ma okolo 20tys rekordow wiec pobieram najpierw z niej id rekordu wedlug szukanego warunku, zapisuje do tablicy i wedlug tablicy pobieram reszte informacji. Robie tak ze wzgledu ze ilosc wynikow dziele na strony, wiec dokladniejsze info pobieram tylko na odpowiednia strone.
Go to the top of the page
+Quote Post
Kpt
post
Post #4





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

wookieb - nie chce tego robic, bo nie mam schematu :-) W kazdym razie lacze polowe bazy to jet ok 10 tabel. Ogladanie zapytania nic nie da. Chodzi mi tylko o to czy sluszna jest koncepcja :-) Z zapytania nic wyciac sie nie da , co najwyzej mozna je podzielic na kawalki.
Go to the top of the page
+Quote Post
ts23
post
Post #5





Grupa: Zarejestrowani
Postów: 12
Pomógł: 1
Dołączył: 20.03.2009

Ostrzeżenie: (0%)
-----

masz indeksy pozakładane gdzie trzeba?
Go to the top of the page
+Quote Post
bazyliszek83
post
Post #6





Grupa: Zarejestrowani
Postów: 81
Pomógł: 6
Dołączył: 24.07.2007
Skąd: Poznań

Ostrzeżenie: (0%)
-----

Przede wszystkim używaj "LEFT JOIN". Nie chce mi się wierzyć że nie da się tego zapytania skrócić. Może pewne rzeczy można wrzucić do widoków?

No ale dopóki nie poznamy zapytania i struktury to nic wiecej nie pomozemy:)
Go to the top of the page
+Quote Post
erix
post
Post #7





Grupa: Moderatorzy
Postów: 15 467
Pomógł: 1451
Dołączył: 25.04.2005
Skąd: Szczebrzeszyn/Rzeszów
Kod
wookieb - nie chce tego robic, bo nie mam schematu :-)

[SQL] pobierz, plaintext 
  1. SHOW CREATE TABLE
[SQL] pobierz, plaintext
Go to the top of the page
+Quote Post
Kpt
post
Post #8





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

Khy lhy, troszke iinna prosba '^^)) Prosze mi polecic DOBRA ksiazke do mysql. Moja wiedza w tym temacie konczy sie na rzeczach podstawowych i glownie w tym tkwi problem. Potrzebuje czegos w miare przystepnego, ale niekoniecznie podstaw. Cos niecow w szkole mialem, ale jak czlowiek z czegos nie korzysta, to wiedza szybko odparowuje :-/
bazyliszek83 - no wlasnie JOIN moglby mi tutaj bardzo pomoc. Zagladalem do mana, ale do konca nie czaje jak sie go uzywa :-/ Nie ma sensu zeby dalej drazyc temat. Trzeba doczytac. Macie jakies typy - odpowiednich ksiazek?
Go to the top of the page
+Quote Post
TomASS
post
Post #9





Grupa: Zarejestrowani
Postów: 1 660
Pomógł: 13
Dołączył: 9.06.2004
Skąd: Wrocław i okolice

Ostrzeżenie: (0%)
-----

@Kpt
Cytat
W bazie jest ok 3 tys pozycji a bedzie wiecej...

To nie wiele - nawet jak będziesz miał milion, to i tak będzie niewiele.

Spróbuj tak:
1. masz w ogóle indeksy pozakładane na tabele?
2. Użyj polecenia EXPLAIN zapytanie, czyli np:
[SQL] pobierz, plaintext 
  1. EXPLAIN SELECT * FROM operacje
[SQL] pobierz, plaintext

które wskaże Ci czy zapytanie korzysta z indeksów (resztę o EXPLAIN poczytaj na google)
3. zamień wszystkie pola VARCHAR i TEXT na CHAR, to co możesz zamień na pola typu ENUM
4. Ustaw klucze unique jeśli możesz
5. Zapytanie konstruuj tak aby pierwszy warunek WHERE odrzucał jak najwięcej rekordów
6. Przeanalizuj SlowQueries i wyświetl informacje o działaniu serwera MySQL
7. Postaraj się zamienić połączenie w where w left joina zamiast
[SQL] pobierz, plaintext 
  1. SELECT * FROM tab1, tab2 WHERE ID.tab1=ID.tab2;
[SQL] pobierz, plaintext

daj
[SQL] pobierz, plaintext 
  1. SELECT * FROM tab1 LEFT JOIN tab2 ON (ID.tab1=ID.tab2);
[SQL] pobierz, plaintext

8. Wpisz sobie w google "optymalizacja zapytań SQL" - dostaniesz tysiące wyników

@bazyliszek
Cytat
Nie chce mi się wierzyć że nie da się tego zapytania skrócić.

Krótsze zapytanie wcale nie wykonuje się szybciej.

Cytat
Może pewne rzeczy można wrzucić do widoków?

A co to da? Skomplikuje tylko sprawę. Widzisz, że chłopak ma 3tyś rekordów, a więc nie powinno to być za skomplikowane zapytanie.

@kpt
Cytat
Prosze mi polecic DOBRA ksiazke do mysql

Do optymalizacji to polecam
SQL Sztuka Programowania link
Go to the top of the page
+Quote Post
bazyliszek83
post
Post #10





Grupa: Zarejestrowani
Postów: 81
Pomógł: 6
Dołączył: 24.07.2007
Skąd: Poznań

Ostrzeżenie: (0%)
-----

Cytat(TomASS @ 2.04.2009, 15:01:23 ) *
@Kpt

To nie wiele - nawet jak będziesz miał milion, to i tak będzie niewiele.

Spróbuj tak:
1. masz w ogóle indeksy pozakładane na tabele?
2. Użyj polecenia EXPLAIN zapytanie, czyli np:
[SQL] pobierz, plaintext 
  1. EXPLAIN SELECT * FROM operacje
[SQL] pobierz, plaintext

które wskaże Ci czy zapytanie korzysta z indeksów (resztę o EXPLAIN poczytaj na google)
3. zamień wszystkie pola VARCHAR i TEXT na CHAR, to co możesz zamień na pola typu ENUM
4. Ustaw klucze unique jeśli możesz
5. Zapytanie konstruuj tak aby pierwszy warunek WHERE odrzucał jak najwięcej rekordów
6. Przeanalizuj SlowQueries i wyświetl informacje o działaniu serwera MySQL
7. Postaraj się zamienić połączenie w where w left joina zamiast
[SQL] pobierz, plaintext 
  1. SELECT * FROM tab1, tab2 WHERE ID.tab1=ID.tab2;
[SQL] pobierz, plaintext

daj
[SQL] pobierz, plaintext 
  1. SELECT * FROM tab1 LEFT JOIN tab2 ON (ID.tab1=ID.tab2);
[SQL] pobierz, plaintext

8. Wpisz sobie w google "optymalizacja zapytań SQL" - dostaniesz tysiące wyników

@bazyliszek

Krótsze zapytanie wcale nie wykonuje się szybciej.
A co to da? Skomplikuje tylko sprawę. Widzisz, że chłopak ma 3tyś rekordów, a więc nie powinno to być za skomplikowane zapytanie.

@kpt

Do optymalizacji to polecam
SQL Sztuka Programowania link


Troche trudno coś doradzić jak nie ma możliwości poznania głębiej problemu. Kolega ma narazie 3 tys, moze kiedyś będzie to w milionach. Tego nie wiemy. Właściwie nie za wiele wiemy:)

Co do długości zapytania, nie mow mi proszę że joinowanie 5 zamiast 10 tabel nic nie zmieni:)

Ale ta dyskusja nie ma większego sensu. Kolega by musiał jasno sprecyzować swoje problemy a tego niestety nie może uczynić.
Go to the top of the page
+Quote Post
TomASS
post
Post #11





Grupa: Zarejestrowani
Postów: 1 660
Pomógł: 13
Dołączył: 9.06.2004
Skąd: Wrocław i okolice

Ostrzeżenie: (0%)
-----

Cytat
Troche trudno coś doradzić jak nie ma możliwości poznania głębiej problemu.

Są ogólne, uniwersalne zasady, które prawie zawsze się sprawdzają. Widzisz, że prosi o książkę, więc szuka uniwersalnych zasad, a nie optymalizacji konkretnego zapytania.
Cytat
Kolega ma narazie 3 tys, moze kiedyś będzie to w milionach.

Do czasu jak będzie obsługiwał miliony sam do tego dojdzie :-)
Cytat
Co do długości zapytania, nie mow mi proszę że joinowanie 5 zamiast 10 tabel nic nie zmieni:)

Jak nie ma indeksów, baz źle zaprojektowana, złe pola itd... to "joinowanie" nic nie zmieni. Dlaczego mówisz, że 5 zamiast 10 tabela? Kolega wyraźnie pisze o 10ciu. I joinowanie tej liczby nie zmieni.
Go to the top of the page
+Quote Post
Kpt
post
Post #12





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

Do optymalizacji to polecam
SQL Sztuka Programowania link

Wielkie dzieki, o takie "cus" mi chodzilo :-) Zaraz ja sobie zamowie.

Poza tym nie macie sie o co sprzeczac. Kazda uwaga moze byc cenna. Pobawilem sie joinem i wiem, ze wykonywanie zapytania trwa tyle, jakby przeszukiwal z pareset tysiecy rekordow a nie 3k poniewaz jest ono zle skonstruowane. Mysle, ze baza az tak skomplikowana i tragiczna nie jest. Pomajstruje z joinem i czegos sie przy okazji dowiem. Mam nadzieje ze w podanej ksiazce znajde tez podpowiedzi i zasady jakich najlepiej przestrzegac przy projektowaniu bazy :-) Wielkie dzieki wszystkim EOT

Ten post edytował Kpt 2.04.2009, 18:04:13
Go to the top of the page
+Quote Post
TomASS
post
Post #13





Grupa: Zarejestrowani
Postów: 1 660
Pomógł: 13
Dołączył: 9.06.2004
Skąd: Wrocław i okolice

Ostrzeżenie: (0%)
-----

Cytat
Pobawilem sie joinem i wiem, ze wykonywanie zapytania trwa tyle, jakby przeszukiwal z pareset tysiecy rekordow a nie 3k poniewaz jest ono zle skonstruowane.

To masz te indeksy czy nie?


Wracając do:
Cytat
Co do długości zapytania, nie mow mi proszę że joinowanie 5 zamiast 10 tabel nic nie zmieni:)


Mam dwie tabele, jedna powyżej 1mln rekordów a druga powyżej 150 tyś. Daję LEFT JOINA:
[SQL] pobierz, plaintext 
  1. SELECT * FROM np_operacje AS O LEFT JOIN np_transporty AS T ON (O.ID_transport=T.ID);
[SQL] pobierz, plaintext

Czas 0.0015s

Daję WHERA:
[SQL] pobierz, plaintext 
  1. SELECT * FROM np_operacje AS O, np_transporty AS T WHERE O.ID_transport = T.ID
[SQL] pobierz, plaintext

Czas..... 0.0014s

Czyli jak masz indeksy, to tak czy siak jest dobrze i joinowanie nie zawsze pomoże.
Go to the top of the page
+Quote Post
Kpt
post
Post #14





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

Cytat(TomASS @ 2.04.2009, 17:19:13 ) *
To masz te indeksy czy nie?

hMm a to bez indeksow tez da sie polaczyc tabele?
Cytat
Wracając do:
Mam dwie tabele, jedna powyżej 1mln rekordów a druga powyżej 150 tyś. Daję LEFT JOINA:
[SQL] pobierz, plaintext 
  1. SELECT * FROM np_operacje AS O LEFT JOIN np_transporty AS T ON (O.ID_transport=T.ID);
[SQL] pobierz, plaintext
.

Zarabiscie, u mnie 0.0013 sekund(y) dla 3k, przy czym skladam 4 tabele w ten sposob. Tez jakos nie widze roznicy miedzy joinem a normalnym skladaniem.
Jest tylko jedna kwestia, ktora miesza mi szyki w klauzuli where 
[SQL] pobierz, plaintext 
  1. (dokumenty.id_dokumentu = dokumenty_spr_usl.id_dokumentu OR dokumenty.id_dokumentu = wykaz_kp.id_dokumentu)
[SQL] pobierz, plaintext

Poniewaz poszczegolne dokumenty roznia sie a co za tym idzie trzeba skladowac rozne pola i wartosci - inne dane zawiera dokument kp inne dokument wz itd, itp to rozbilem je na osobne tabele kazda z nich ma oczywiscie klucz obcy w postaci id_dokumentu, ktory jest kluczem glownym w tabeli dokumenty (dla mnie wyjsciowej)... Gdyby dalo sie zastapic tego ORa czyms iinym szukanie trwaloby jw, a tak przy dolozeniu jeszcze 2 warunkow w where jest katastrofa... Moglbym to tez zrobic inaczej na podstawie wynikow odpytac pozniej baze jeszcze raz juz o dane z konkretnej tabeli w zaleznosci od tego jaki to dokument czy kp czy faktura czy jeszcze cos innego. Moj sql jest zbyt ubogi ;-/ Jakis if by sie tu przydal...
Go to the top of the page
+Quote Post
TomASS
post
Post #15





Grupa: Zarejestrowani
Postów: 1 660
Pomógł: 13
Dołączył: 9.06.2004
Skąd: Wrocław i okolice

Ostrzeżenie: (0%)
----- 

[SQL] pobierz, plaintext 
  1. (dokumenty.id_dokumentu = dokumenty_spr_usl.id_dokumentu OR dokumenty.id_dokumentu = wykaz_kp.id_dokumentu)
[SQL] pobierz, plaintext

A masz (jak nie to daj) indeksy na polach:

dokumenty.id_dokumentu
dokumenty_spr_usl.id_dokumentu
wykaz_kp.id_dokumentu

?
Go to the top of the page
+Quote Post
Kpt
post
Post #16





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
----- 

[SQL] pobierz, plaintext 
  1. Dokumenty 
  2. +----------------+-------------+------+-----+------------+----------------+
  3. | FIELD          | Type        | NULL | KEY | DEFAULT    | Extra          |
  4. +----------------+-------------+------+-----+------------+----------------+
  5. | id_dokumentu   | int(5)      | NO   | PRI | NULL       | AUTO_INCREMENT |
  6. | id_num_dok     | int(7)      | NO   |     |            |                |
  7. | data_wyst      | date        | NO   |     | 0000-00-00 |                |
  8. | id_klienta     | int(5)      | YES  |     | 0          |                |
  9. | komentarz      | varchar(60) | YES  |     | NULL       |                |
  10. | czy_zaplacono  | char(1)     | YES  |     | NULL       |                |
  11. | id_pracownika  | tinyint(4)  | NO   |     | 0          |                |
  12. | id_rodzaju_dok | tinyint(2)  | NO   |     | 0          |                |
  13. +----------------+-------------+------+-----+------------+----------------+
  14.  
  15. dokumenty_spr_usl
  16. +---------------------+------------+------+-----+------------+----------------+
  17. | FIELD               | Type       | NULL | KEY | DEFAULT    | Extra          |
  18. +---------------------+------------+------+-----+------------+----------------+
  19. | id_dok_spr          | int(5)     | NO   | PRI | NULL       | AUTO_INCREMENT |
  20. | cena_jed_spr_brutto | double     | YES  |     | NULL       |                |
  21. | data_spr            | date       | NO   |     | 0000-00-00 |                |
  22. | data_pocz           | date       | NO   |     | 0000-00-00 |                |
  23. | data_kon            | date       | NO   |     | 0000-00-00 |                |
  24. | id_opcji            | tinyint(4) | NO   |     | 0          |                |
  25. | czy_rabat           | char(1)    | NO   |     |            |                |
  26. | czy_wyslano         | char(1)    | NO   |     |            |                |
  27. | id_dokumentu        | int(5)     | NO   |     | 0          |                |
  28. +---------------------+------------+------+-----+------------+----------------+
[SQL] pobierz, plaintext


Trzeciej nie wklejam - szkoda miejsca. Jak mowilem w momencie dodania warunku do where szukania konkretnego pola robi sie kaszanka, dopoki w where jest tylko (dokumenty.id_dokumentu = dokumenty_spr_usl.id_dokumentu or dokumenty.id_dokumentu = wykaz_kp.id_dokumentu) -ujdzie
Go to the top of the page
+Quote Post
dr_bonzo
post
Post #17





Grupa: Przyjaciele php.pl
Postów: 5 724
Pomógł: 259
Dołączył: 13.04.2004
Skąd: N/A

Ostrzeżenie: (0%)
-----

1. POkaz wszystkie uzywane tabele.
2. POkaz ta SQLke
3. Pokaz EXPLAIN do tej sqlki
bo tak to se mozna o ogolnikach tylko pogadac.
Go to the top of the page
+Quote Post
Kpt
post
Post #18





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
----- 

[SQL] pobierz, plaintext 
  1. SELECT DISTINCT (
  2. dokumenty.id_dokumentu
  3. )id_dokumentu, data_wyst, rok, rodzaje_dokumentow.id_rodzaju_dok id_rodzaju_dok, numery_dok.numer_dok numer_dok, rok, rodzaj_dokumentu
  4. FROM dokumenty, rodzaje_dokumentow, dokumenty_spr_usl, klienci, numery_dok, lata_dok, wykaz_kp
  5. WHERE dokumenty.id_rodzaju_dok = rodzaje_dokumentow.id_rodzaju_dok AND (
  6. dokumenty.id_dokumentu = dokumenty_spr_usl.id_dokumentu OR dokumenty.id_dokumentu = wykaz_kp.id_dokumentu
  7. ) AND klienci.id_klienta = dokumenty.id_klienta AND dokumenty.id_num_dok = numery_dok.id_num_dok AND numery_dok.id_roku = lata_dok.id_roku AND klienci.nazwisko = 'Kowalski'
[SQL] pobierz, plaintext


cos
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wykaz_kp ALL NULL NULL NULL NULL 34 Using temporary
1 SIMPLE dokumenty_spr_usl ALL NULL NULL NULL NULL 3268
1 SIMPLE dokumenty ALL PRIMARY NULL NULL NULL 3269 Range checked for each record (index map: 0x1)
1 SIMPLE klienci eq_ref PRIMARY PRIMARY 4 firma.dokumenty.id_klienta 1 Using where
1 SIMPLE rodzaje_dokumentow eq_ref PRIMARY PRIMARY 1 firma.dokumenty.id_rodzaju_dok 1
1 SIMPLE numery_dok eq_ref PRIMARY PRIMARY 4 firma.dokumenty.id_num_dok 1
1 SIMPLE lata_dok eq_ref PRIMARY PRIMARY 1 firma.numery_dok.id_roku 1
Go to the top of the page
+Quote Post
dr_bonzo
post
Post #19





Grupa: Przyjaciele php.pl
Postów: 5 724
Pomógł: 259
Dołączył: 13.04.2004
Skąd: N/A

Ostrzeżenie: (0%)
-----

No juz lepiej - cos widac:

* Sproboj uzyc UNION dla osobnych warunkow OR (wywnioskowalem z tego co dostalem z dokument jest dokumenty_spr_usl lub wykaz_kp - nigdy w obu na raz, nie? edit: No chyba jednak nie ...)
* Brak indeksow w dokumenty_spr_usl.id_dokumentu ?
* Brak indeksow w wykaz_kp.id_dokumentu ?

Ten post edytował dr_bonzo 3.04.2009, 10:08:06
Go to the top of the page
+Quote Post
Kpt
post
Post #20





Grupa: Zarejestrowani
Postów: 17
Pomógł: 0
Dołączył: 17.10.2004

Ostrzeżenie: (0%)
-----

Cytat(dr_bonzo @ 3.04.2009, 09:07:08 ) *
No juz lepiej - cos widac:

* Sproboj uzyc UNION dla osobnych warunkow OR (wywnioskowalem z tego co dostalem z dokument jest dokumenty_spr_usl lub wykaz_kp - nigdy w obu na raz, nie? edit: No chyba jednak nie ...)


Owszem, tak jak piszesz, dokumenty.id_dokumentu nie koniecznie musi byc = dokumenty_spr_usl.id_dokumentu moze to byc wykaz_kp.id_dokumentu
stad dalem tego OR wkazdym razie w ktorejs z tych tabel MUSI byc
Cytat
* Brak indeksow w dokumenty_spr_usl.id_dokumentu ?
* Brak indeksow w wykaz_kp.id_dokumentu ?

Hm raczej ich nie brakuje :-) po prostu ten OR jest IMO nie na miejscu.
Go to the top of the page
+Quote Post
« Następny starszy · Przedszkole · Następny nowszy »
 

2 Stron V   1 2 >
Reply to this topicStart new topic
2 Użytkowników czyta ten temat (2 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 

Tryb wyświetlania: Standardowy · Przełącz na: Linearny+ · Przełącz na: Drzewo

Śledź ten temat · Wyślij temat na e-mail · Wydrukuj ten temat · Subskrybuj to forum

RSS Aktualny czas: 23.08.2025 - 02:20
Powered By IP.Board © 2025  IPS, Inc.
All changes by PHP.pl Administrators
Hosting zapewnia NQ.pl hosting, trac, svn