Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Dlugie zapytanie przy JOIN i ORDER BY
Forum PHP.pl > Forum > Bazy danych > MySQL
gpi
20.04.2012, 09:44:55
Witam,
mam dosc spora baze (ok. 3 mln rekordow) z firmami w ktorej pojawil mi sie problem przy zapytaniu ktore pobiera dane firmy wg. okreslonej branzy, a wyniki sortuje po polu liczbowym - 'priorytet'. Tabele mam w innoDB a czas query siega 20sekund:

Oto zapytanie:
[SQL] pobierz, plaintext 
  1. SELECT `firma`.id  FROM `firmy` AS `firma` LEFT JOIN `firma_branza` AS `firmabranza` 
  2. ON (`firma`.`id` = `firmabranza`.`id_firma`) WHERE `id_branza_1` = '455' 
  3. AND `status` = '1' ORDER BY `priorytet` DESC LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Budowa tabeli firmy:
[SQL] pobierz, plaintext 
  1. CREATE TABLE `firmy` (
  2. 	`id` INT(10) NOT NULL AUTO_INCREMENT,
  3. 	`status` TINYINT(1) NULL DEFAULT '1',
  4. 	`nazwa_rejestrowa` VARCHAR(255) NULL DEFAULT NULL,
  5. 	`kod_pocztowy` CHAR(6) NULL DEFAULT NULL,
  6. 	`id_wojewodztwo` TINYINT(3) NOT NULL,
  7. 	`miejscowosc` VARCHAR(50) NULL DEFAULT NULL,
  8. 	`ulica_nr` VARCHAR(100) NULL DEFAULT NULL,
  9. 	`telefon1` VARCHAR(50) NULL DEFAULT NULL,
  10. 	`fax` VARCHAR(20) NULL DEFAULT NULL,
  11. 	`email` VARCHAR(50) NULL DEFAULT NULL,
  12. 	`www` VARCHAR(50) NULL DEFAULT NULL,
  13. 	`priorytet` SMALLINT(3) NOT NULL DEFAULT '1',
  14. 	PRIMARY KEY (`id`, `priorytet`),
  15. 	INDEX `FK_firmy_users` (`id_user`),
  16. 	INDEX `index_id_wojewodztwo` (`id_wojewodztwo`),
  17. 	INDEX `nazwa_rejestrowa` (`nazwa_rejestrowa`),
  18. 	INDEX `status_priorytet` (`status`, `priorytet`)
  19. )
  20. COLLATE='utf8_general_ci'
  21. ENGINE=InnoDB;
[SQL] pobierz, plaintext


Budowa tabeli zlaczeniowej branz:
[SQL] pobierz, plaintext 
  1. CREATE TABLE `firma_branza` (
  2. 	`id` INT(10) NOT NULL AUTO_INCREMENT,
  3. 	`id_firma` INT(10) NOT NULL,
  4. 	`id_branza_1` INT(10) NULL DEFAULT NULL,
  5. 	`id_branza_2` INT(10) NULL DEFAULT NULL,
  6. 	`id_branza_3` INT(10) NULL DEFAULT NULL,
  7. 	PRIMARY KEY (`id`),
  8. 	INDEX `FK_firma_branza_firmy` (`id_firma`),
  9. 	INDEX `id_branza_2` (`id_branza_2`),
  10. 	INDEX `id_branza` (`id_branza_1`, `id_branza_2`, `id_branza_3`),
  11. 	CONSTRAINT `firma_branza_ibfk_1` FOREIGN KEY (`id_firma`) REFERENCES `firmy` (`id`),
  12. 	CONSTRAINT `firma_branza_ibfk_2` FOREIGN KEY (`id_branza_2`) REFERENCES `branze` (`id`)
  13. )
  14. COLLATE='utf8_general_ci'
  15. ENGINE=InnoDB;
[SQL] pobierz, plaintext


Wynik explain zapytania pokazuje taki rezultat (niepokojacy dla tabeli firma_branza):

[HTML] pobierz, plaintext 
  1. <table name="UnknownTable">
  2. 	<row>
  3. 		<id>1</id>
  4. 		<select_type>SIMPLE</select_type>
  5. 		<table>firmabranza</table>
  6. 		<type>ref</type>
  7. 		<possible_keys>FK_firma_branza_firmy,id_branza</possible_keys>
  8. 		<key>id_branza</key>
  9. 		<key_len>5</key_len>
  10. 		<ref>const</ref>
  11. 		<rows>129168</rows>
  12. 		<Extra>Using where; Using temporary; Using filesort</Extra>
  13. 	</row>
  14. 	<row>
  15. 		<id>1</id>
  16. 		<select_type>SIMPLE</select_type>
  17. 		<table>firma</table>
  18. 		<type>ref</type>
  19. 		<possible_keys>PRIMARY,status_priorytet</possible_keys>
  20. 		<key>PRIMARY</key>
  21. 		<key_len>4</key_len>
  22. 		<ref>okf.firmabranza.id_firma</ref>
  23. 		<rows>1</rows>
  24. 		<Extra>Using where</Extra>
  25. 	</row>
  26. </table>
[HTML] pobierz, plaintext



Czy ktos pomoze w probie ustawienia odpowiednich indeksow zeby to dzialanie zoptymalizowac ?
alegorn
20.04.2012, 10:09:30
czas masz z uwagi na to ze zapytanie wymaga tabeli tymczasowej (wymuszone najpewniej przez order by)

z czystej ciekawosci sprawdź (nie testowalem zapisu) :

[SQL] pobierz, plaintext 
  1. SELECT 
  2. 	`firma`.id 
  3. FROM 
  4. 	`firma_branza` AS `firmabranza`
  5. 	JOIN `firmy` AS `firma`  ON (`firma`.`id` = `firmabranza`.`id_firma` AND `status` =1) 
  6. WHERE 
  7. 	`id_branza_1` = 455
  8. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


sprawdz czasy, wydaje mi sie ze nawet jesli dodasz order by - takie zlaczenie powino zadzialac ciut lepiej.

j.
rocktech.pl
20.04.2012, 10:12:46
Witam.

[SQL] pobierz, plaintext 
  1. SELECT * FROM firmy PROCEDURE ANALYSE(100000);
  2. SELECT * FROM firma_branza PROCEDURE ANALYSE(100000);
[SQL] pobierz, plaintext


Spokojnie typ pola priorytet można zmienić na ENUM.

Co do samego zapytania.

[SQL] pobierz, plaintext 
  1. SELECT 
  2.     firma.id
  3. FROM
  4.     firmy AS firma USE INDEX(id_branza)
  5.         LEFT JOIN
  6.     firma_branza AS firmabranza ON (firma.id = firmabranza.id_firma)
  7. WHERE
  8.     id_branza_1 = '455' AND STATUS = '1'
  9. ORDER BY priorytet DESC
  10. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext
kiciafu
20.04.2012, 10:15:35
Nie potrafię, odpowiedzieć jakby to można było przyspieszyć ale mam pytanie: celowo robiłeś trzy branże dla każdej firmy? A jak firma należy do czeterech branż? Dla jednej branży masz przyporządkowaną jedną a w dwóch pozostałych NULL? Ja bym to rozbił na 3 tabele choć na 100% byłoby jeszcze wolniej, choć ładniej smile.gif
alegorn
20.04.2012, 10:22:35
@rocktech.pl: przeciez tabela firma nie ma takiego indexu...

@gpi: jesli chodzi o indeksy, lepiej ci zadzialaja 3 osobne indeksy na branze, niz jeden dla wszystkich (chyba ze zawsze korzystasz z pola id_branza_1)
tzn chodzi mi o to, ze jeseli bedziesz szukal tylko po polu id_branza_3 - to ten index nie zostanie wykorzystany.

zegarek84
20.04.2012, 10:29:56
miałem napisać podobnie do @rocktech.pl... ale... dodam, iż kolejność warunków w WHERE także ma znaczenie, delikatnie ale nie znacznie przyśpieszyłbyś zmieniając kolejność warunku gdyż mniej sprawdzeń tymczasowych by zrobiło... jednak tabelą główną do złączeń powinna być ta, gdzie jest główny warunek lub gdzie są jakieś sensowne indeksy...

podsumowując to najwydajniej nie zmieniając struktury tabeli to zapytanie zaproponowane przez @rocktech.pl:
[SQL] pobierz, plaintext 
  1. SELECT f.id FROM firma_branza AS fb
  2. LEFT JOIN firmy AS f ON fb.id_firma = f.id
  3. WHERE fb.id_branza_1 = 455 AND f.`status`
  4. ORDER BY f.priorytet DESC
  5. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext

a Twoje które powinno ciutkę szybciej zadziałać:
[SQL] pobierz, plaintext 
  1. SELECT f.id FROM firmy AS f
  2. LEFT JOIN firma_branza AS fb ON f.id = fb.id_firma
  3. WHERE f.`status` AND fb.id_branza_1 = 455
  4. ORDER BY f.priorytet DESC
  5. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext
redeemer
20.04.2012, 10:41:09
Abstrahując od zapytania i indexów, jeżeli masz taką możliwość to możesz też stworzyć system plików tymczasowych, który będzie działał w pamięci RAM.
pmir13
20.04.2012, 21:10:35
Czym różni się LEFT JOIN od INNER JOINa? LEFT JOIN zwraca również te rekordy z lewej tabeli, dla których nie ma rekordów w prawej tabeli spełniających warunek złączenia. Wtedy pola z drugiej tabeli przyjmują wartość NULL. W przypadku zapytania ze startera wątku dodatkowo ograniczamy rekordy z prawej tabeli warunkiem id_branza_1 = 455, czyli wcale nie chcemy niczego co by było NULL.

Wniosek jest taki, że nasz LEFT JOIN przy tym warunku staje się tak naprawdę INNER JOINem i optimizer tak właśnie go przepisał, skoro pierwsza tabela w wynikach explain jest właśnie prawa. Prawdopodobnie widząc ten dość 'silny' warunek, bo w końcu porównujemy pole z jedną, konkretną, dosyć wysoką wartością optimizer spodziewał się, że szybciej będzie zacząć od tabeli firmy_branza, ograniczyć rekordy do tych, które ten warunek spełniają, po czym połączyć to z tabelą firmy, sprawdzić status i posortować to co zostanie po priorytecie. Stąd taki a nie inny plan wykonania tego zapytania. Tylko że okazuje się, że do sortowania mamy 129168 rekordów w tymczasowej tabeli, co musi być bolesne, nie ma bata, a musimy posortować wszystkie, bo przecież najwyższy priorytet może być w ostatnim rekordzie.

Biorąc pod uwagę jednak stosunkowo małą liczbie rekordów, które chcemy obejrzeć - LIMIT 20, a także fakt, że są to pierwsze rekordy - OFFSET 0, możemy się spodziewać, że gdybyśmy zaczęli od tabeli firmy, znajdując po indeksie status_priorytet najwyższe priorytety z właściwym statusem i sprawdzali czy połączone rekordy z tabeli firmy_branza mają id_branza_1 = 455 (a szansa na to jest niemała, akurat dla tej właśnie wartości), to moglibyśmy szybko przerwać, po znalezieniu 20 takich rekordów. Oczywiście gdyby tego limitu nie było, albo offset byłby wysoki, albo firm z tej branży byłoby mało, wtedy oryginalny plan wykonania byłby optymalny, w tym jednak konkretnym przypadku mamy do czynienia z klasyczną sytuacją, w której optimizer wybrał złą kolejność złączenia i trzeba mu dopomóc. Podsumowując - możemy spróbować zapytania:

[SQL] pobierz, plaintext 
  1. SELECT `firma`.id FROM `firmy` AS `firma` 
  2. STRAIGHT_JOIN `firma_branza` AS `firmabranza`
  3. WHERE `firma`.`id` = `firmabranza`.`id_firma`
  4.    AND `id_branza_1` = '455'
  5.    AND `status` = '1' 
  6. ORDER BY `priorytet` DESC 
  7. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


gpi
23.04.2012, 08:31:45
Cytat(alegorn @ 20.04.2012, 11:09:30 ) *
czas masz z uwagi na to ze zapytanie wymaga tabeli tymczasowej (wymuszone najpewniej przez order by)

z czystej ciekawosci sprawdź (nie testowalem zapisu) :

[SQL] pobierz, plaintext 
  1. SELECT 
  2. 	`firma`.id 
  3. FROM 
  4. 	`firma_branza` AS `firmabranza`
  5. 	JOIN `firmy` AS `firma`  ON (`firma`.`id` = `firmabranza`.`id_firma` AND `status` =1) 
  6. WHERE 
  7. 	`id_branza_1` = 455
  8. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


sprawdz czasy, wydaje mi sie ze nawet jesli dodasz order by - takie zlaczenie powino zadzialac ciut lepiej.

j.



Minimalnie, ale ewidentie chodzi o ten order by... to zabija moje zapytanie.

Cytat(rocktech.pl @ 20.04.2012, 11:12:46 ) *
Witam.

[SQL] pobierz, plaintext 
  1. SELECT * FROM firmy PROCEDURE ANALYSE(100000);
  2. SELECT * FROM firma_branza PROCEDURE ANALYSE(100000);
[SQL] pobierz, plaintext


Spokojnie typ pola priorytet można zmienić na ENUM.

Co do samego zapytania.

[SQL] pobierz, plaintext 
  1. SELECT 
  2.     firma.id
  3. FROM
  4.     firmy AS firma USE INDEX(id_branza)
  5.         LEFT JOIN
  6.     firma_branza AS firmabranza ON (firma.id = firmabranza.id_firma)
  7. WHERE
  8.     id_branza_1 = '455' AND STATUS = '1'
  9. ORDER BY priorytet DESC
  10. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Typy pol mialem calkiem dobrze ponazywane. Pozmienialem tylko dla status, priorytet z TINYINT 1 na ENUM. Poprawa jest ale niewielka.
Dziwne ze ANALYSE z pola Varchar 255 proponuje jako bardziej optymalne TINYTEXT. Czyzby wyszukiwanie w TINYTEXT bylo szybsze niz w Vatcharach ?

Co do twojej propozycji zmiany zapytania USE INDEX(id_branza) wywala blad bo indeks id_branza jest na tabeli "firma_branza" a nie na "firma"


Cytat(kiciafu @ 20.04.2012, 11:15:35 ) *
Nie potrafię, odpowiedzieć jakby to można było przyspieszyć ale mam pytanie: celowo robiłeś trzy branże dla każdej firmy? A jak firma należy do czeterech branż? Dla jednej branży masz przyporządkowaną jedną a w dwóch pozostałych NULL? Ja bym to rozbił na 3 tabele choć na 100% byłoby jeszcze wolniej, choć ładniej smile.gif


Troche zle to interpretujesz. Tabela "firma_branza" jest tabela laczaca tabele "firma" i "branza" (ktorej nie podawalem bo zapytanie jej nie dotyczy). id_branza_1, id_branza_2, id_branza_3 oznacza strukture drzewa branz, natomiast kazdy nowy rekord w "firma_branza" oznacza zaklasyfikowanie firmy do kolejnej branzy - takich klasyfikacji moze byc wiele.

Cytat(alegorn @ 20.04.2012, 11:22:35 ) *
@gpi: jesli chodzi o indeksy, lepiej ci zadzialaja 3 osobne indeksy na branze, niz jeden dla wszystkich (chyba ze zawsze korzystasz z pola id_branza_1)
tzn chodzi mi o to, ze jeseli bedziesz szukal tylko po polu id_branza_3 - to ten index nie zostanie wykorzystany.



Wlasnie zawsze podaje caly ciag branz, moge miec:
WHERE id_branza_1='1' lub
WHERE id_branza_1='1' AND id_branza_2='56' lub
WHERE id_branza_1='1' AND id_branza_2='56' AND id_branza_3='16'

nigdy samo id_branza_2, id_branza_3
zegarek84
23.04.2012, 08:39:37
Cytat(gpi @ 23.04.2012, 09:31:45 ) *
Minimalnie, ale ewidentie chodzi o ten order by... to zabija moje zapytanie.

a na ten priorytet nie możesz nałożyć choć jakichś indeksów?? nie jestem wyspecjalizowany w bazach ale na moje oko by to optymalnie chodziło przed where jest order i dopiero po kolei są sprawdzane warunki coby optymalniej było dalej na limit... tak tylko strzelam... jeśli na prorytet nie ma indeksu to przy wyszukiwaniu musi i tak przelecieć całą tabelę i ją odpowiednio ułożyć dosłownie za każdym razem...
gpi
23.04.2012, 08:45:01
Cytat(pmir13 @ 20.04.2012, 22:10:35 ) *
[SQL] pobierz, plaintext 
  1. SELECT `firma`.id FROM `firmy` AS `firma` 
  2. STRAIGHT_JOIN `firma_branza` AS `firmabranza`
  3. WHERE `firma`.`id` = `firmabranza`.`id_firma`
  4.    AND `id_branza_1` = '455'
  5.    AND `status` = '1' 
  6. ORDER BY `priorytet` DESC 
  7. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Tutaj mam najlepszy rezultat. Musze poprzerabiac moje zapytania w tym kierunku.
Dzieki wielkie.
Pilsener
23.04.2012, 10:51:22
Jeśli potrzebujesz tabeli firma_branza tylko po to, by wyszukać firmy z określonych branż to nie potrzebujesz w ogóle joina. Spróbuj jeszcze tak:

[SQL] pobierz, plaintext 
  1.     SELECT `firma`.id FROM `firmy` AS `firma`
  2.     WHERE `firma`.id IN (SELECT id_firma FROM firmabranza WHERE id_branza_1=455 AND STATUS=1)
  3.     ORDER BY `priorytet` DESC
  4.     LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Pytanie istotne, to w której tabeli jest pole "priorytet", zakładam, że w "firmy" - jeśli w branżach to trzeba to dodac do podzapytania wraz z limitem i problemu w ogóle nie będzie.
Jeśli trzeba sortować dużą tabelę to problem będzie nawet przy prostym select niestety. Zależy też jak duże są obie tabele.
gpi
23.04.2012, 19:08:32
Cytat(Pilsener @ 23.04.2012, 11:51:22 ) *
Jeśli potrzebujesz tabeli firma_branza tylko po to, by wyszukać firmy z określonych branż to nie potrzebujesz w ogóle joina. Spróbuj jeszcze tak:

[SQL] pobierz, plaintext 
  1.     SELECT `firma`.id FROM `firmy` AS `firma`
  2.     WHERE `firma`.id IN (SELECT id_firma FROM firmabranza WHERE id_branza_1=455 AND STATUS=1)
  3.     ORDER BY `priorytet` DESC
  4.     LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Pytanie istotne, to w której tabeli jest pole "priorytet", zakładam, że w "firmy" - jeśli w branżach to trzeba to dodac do podzapytania wraz z limitem i problemu w ogóle nie będzie.
Jeśli trzeba sortować dużą tabelę to problem będzie nawet przy prostym select niestety. Zależy też jak duże są obie tabele.



Porobilem kilka testow i w zasadzie wydajnosc powyzszego zapytania zblizona jest do tego ktory zaproponowal wczesniejszy przedmowca ze STRAIGHT_JOIN.

Ale trafilem na zagadke: O ile powyzsze zapytanie smiga przyzwoicie ok. 0.3 sek, to jak dodam drugi element warunku id_branza_2 to czas wtedy wzrasta do 240 sek!!!

[SQL] pobierz, plaintext 
  1.  SELECT `firma`.id FROM `firmy` AS `firma`
  2.  WHERE `firma`.id IN (SELECT id_firma FROM firma_branza WHERE id_branza_1=455 AND id_branza_2=548)  AND STATUS=1
  3.  ORDER BY `priorytet` DESC
  4.  LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext


Generalnie sam "Select id_firma from ..." z drugim warunkiem ograniczajacym powinien szybciej zwrocic rekordy i wykonujac go samemu tak faktycznie jest. Natomiast w uzyciu z klauzula "IN" strasznie jest to nieoptymalne u mnie.
zegarek84
23.04.2012, 20:32:00 
[SQL] pobierz, plaintext 
  1. SELECT f.id FROM 
  2.   (SELECT fb.id_firma FROM firma_branza AS fb WHERE fb.id_branza_1 = 455 AND fb.id_branza_2=548) AS fb
  3. LEFT JOIN firmy AS f ON fb.id_firma = f.id
  4. WHERE f.`status`
  5. ORDER BY f.priorytet DESC
  6. LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext

jak sama nazwa mówi to są podzapytania, a podzapytania w warunku WHERE mogą działać rekurencyjnie... np. mi takie cudeńko na pewnej tabeli zadziałało wyświetlając wszystkie wiersze:
[SQL] pobierz, plaintext 
  1. SELECT r.* FROM rodzic AS r WHERE r.id IN (SELECT d.id FROM rodzic AS d WHERE d.id = r.id)
[SQL] pobierz, plaintext

a jak indeksy są pozakładane tam, gdzie nie przynoszą wymiernych korzyści (czytaj zbyt mało unikalnych wartości) to i tak niemal wszystko musi być porównane do tego dochodzą różne przeskoki w poszukiwaniu wartości w bazie...

polecam link:
7 ways to convince MySQL to use the right index

i to tyle w tym temacie...
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.