Forum PHP.pl > [PHP][MYSQL]

Pomoc - Szukaj - Użytkownicy - Kalendarz

Kpt

2.04.2009, 10:04:03

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.

wookieb

2.04.2009, 10:08:08

Wiesz nie zaszkodziłoby pokazac tego zapytania a NAWET struktury tabeli.

trike

2.04.2009, 10:10:14

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.

Kpt

2.04.2009, 10:19:39

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.

ts23

2.04.2009, 10:27:55

masz indeksy pozakładane gdzie trzeba?

bazyliszek83

2.04.2009, 10:30:24

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:)

erix

2.04.2009, 11:18:10

Kod

wookieb - nie chce tego robic, bo nie mam schematu :-)

[SQL] pobierz, plaintext 
SHOW CREATE TABLE
[SQL] pobierz, plaintext

Kpt

2.04.2009, 11:43:11

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?

TomASS

2.04.2009, 14:01:23

@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 
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 
SELECT * FROM tab1, tab2 WHERE ID.tab1=ID.tab2;
[SQL] pobierz, plaintext

daj

[SQL] pobierz, plaintext 
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

bazyliszek83

2.04.2009, 14:29:26

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 
EXPLAIN SELECT * FROM operacje
[SQL] pobierz, plaintext

[SQL] pobierz, plaintext 
SELECT * FROM tab1, tab2 WHERE ID.tab1=ID.tab2;
[SQL] pobierz, plaintext

daj

[SQL] pobierz, plaintext 
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ć.

TomASS

2.04.2009, 16:57:54

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.

Kpt

2.04.2009, 17:41:51

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

TomASS

2.04.2009, 18:19:13

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 
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 
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.

Kpt

2.04.2009, 19:15:18

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 
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 
(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...

TomASS

2.04.2009, 23:27:28

[SQL] pobierz, plaintext 
(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

?

Kpt

3.04.2009, 08:15:44

[SQL] pobierz, plaintext 
Dokumenty 
+----------------+-------------+------+-----+------------+----------------+
| FIELD          | Type        | NULL | KEY | DEFAULT    | Extra          |
+----------------+-------------+------+-----+------------+----------------+
| id_dokumentu   | int(5)      | NO   | PRI | NULL       | AUTO_INCREMENT |
| id_num_dok     | int(7)      | NO   |     |            |                |
| data_wyst      | date        | NO   |     | 0000-00-00 |                |
| id_klienta     | int(5)      | YES  |     | 0          |                |
| komentarz      | varchar(60) | YES  |     | NULL       |                |
| czy_zaplacono  | char(1)     | YES  |     | NULL       |                |
| id_pracownika  | tinyint(4)  | NO   |     | 0          |                |
| id_rodzaju_dok | tinyint(2)  | NO   |     | 0          |                |
+----------------+-------------+------+-----+------------+----------------+
 
dokumenty_spr_usl
+---------------------+------------+------+-----+------------+----------------+
| FIELD               | Type       | NULL | KEY | DEFAULT    | Extra          |
+---------------------+------------+------+-----+------------+----------------+
| id_dok_spr          | int(5)     | NO   | PRI | NULL       | AUTO_INCREMENT |
| cena_jed_spr_brutto | double     | YES  |     | NULL       |                |
| data_spr            | date       | NO   |     | 0000-00-00 |                |
| data_pocz           | date       | NO   |     | 0000-00-00 |                |
| data_kon            | date       | NO   |     | 0000-00-00 |                |
| id_opcji            | tinyint(4) | NO   |     | 0          |                |
| czy_rabat           | char(1)    | NO   |     |            |                |
| czy_wyslano         | char(1)    | NO   |     |            |                |
| id_dokumentu        | int(5)     | NO   |     | 0          |                |
+---------------------+------------+------+-----+------------+----------------+
[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

dr_bonzo

3.04.2009, 08:44:57

1. POkaz wszystkie uzywane tabele.
2. POkaz ta SQLke
3. Pokaz EXPLAIN do tej sqlki
bo tak to se mozna o ogolnikach tylko pogadac.

Kpt

3.04.2009, 09:11:24

[SQL] pobierz, plaintext 
SELECT DISTINCT (
dokumenty.id_dokumentu
)id_dokumentu, data_wyst, rok, rodzaje_dokumentow.id_rodzaju_dok id_rodzaju_dok, numery_dok.numer_dok numer_dok, rok, rodzaj_dokumentu
FROM dokumenty, rodzaje_dokumentow, dokumenty_spr_usl, klienci, numery_dok, lata_dok, wykaz_kp
WHERE dokumenty.id_rodzaju_dok = rodzaje_dokumentow.id_rodzaju_dok AND (
dokumenty.id_dokumentu = dokumenty_spr_usl.id_dokumentu OR dokumenty.id_dokumentu = wykaz_kp.id_dokumentu
) 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

dr_bonzo

3.04.2009, 10: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 ...)
* Brak indeksow w dokumenty_spr_usl.id_dokumentu ?
* Brak indeksow w wykaz_kp.id_dokumentu ?

Kpt

3.04.2009, 11:48:25

Cytat(dr_bonzo @ 3.04.2009, 09:07:08 )

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.

dr_bonzo

3.04.2009, 12:12:21

Cytat(Kpt @ 3.04.2009, 12:48:25 )

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.

No to raczej czy na pewno? I wyrzuc polowe tego ORa - i sprawdz czy zaczna dzialac indexy.

pi_wo

3.04.2009, 15:16:57

Oprócz poprawnych indeksów, jeżeli masz w miarę stałą budowę tego JOIN'a polecam zapoznać się z :

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Ja przy podobnym problemie zaobserwowałem spory wzrost wydajności (i oczywiście bezpieczeństwa, bo odwołujesz się do "Widoku" a nie bezpośrednio do tabeli SQL'owej)

Kpt

3.04.2009, 17:06:47

W kazdym razie UNIONem poszlo bez problemu - prosto i skutecznie.

TomASS

4.04.2009, 19:05:01

A co zwróci Ci zapytanie:

[SQL] pobierz, plaintext 
SHOW INDEX FROM wykaz_kp
[SQL] pobierz, plaintext

oraz

[SQL] pobierz, plaintext 
SHOW INDEX FROM dokumenty_spr_usl
[SQL] pobierz, plaintext

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.