Dlugie zapytanie przy JOIN i ORDER BY Opcje

[gpi]

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 
SELECT `firma`.id  FROM `firmy` AS `firma` LEFT JOIN `firma_branza` AS `firmabranza` 
ON (`firma`.`id` = `firmabranza`.`id_firma`) WHERE `id_branza_1` = '455' 
AND `status` = '1' ORDER BY `priorytet` DESC LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext 

Budowa tabeli firmy:

[SQL] pobierz, plaintext 
CREATE TABLE `firmy` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`status` TINYINT(1) NULL DEFAULT '1',
	`nazwa_rejestrowa` VARCHAR(255) NULL DEFAULT NULL,
	`kod_pocztowy` CHAR(6) NULL DEFAULT NULL,
	`id_wojewodztwo` TINYINT(3) NOT NULL,
	`miejscowosc` VARCHAR(50) NULL DEFAULT NULL,
	`ulica_nr` VARCHAR(100) NULL DEFAULT NULL,
	`telefon1` VARCHAR(50) NULL DEFAULT NULL,
	`fax` VARCHAR(20) NULL DEFAULT NULL,
	`email` VARCHAR(50) NULL DEFAULT NULL,
	`www` VARCHAR(50) NULL DEFAULT NULL,
	`priorytet` SMALLINT(3) NOT NULL DEFAULT '1',
	PRIMARY KEY (`id`, `priorytet`),
	INDEX `FK_firmy_users` (`id_user`),
	INDEX `index_id_wojewodztwo` (`id_wojewodztwo`),
	INDEX `nazwa_rejestrowa` (`nazwa_rejestrowa`),
	INDEX `status_priorytet` (`status`, `priorytet`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
[SQL] pobierz, plaintext 

Budowa tabeli zlaczeniowej branz:

[SQL] pobierz, plaintext 
CREATE TABLE `firma_branza` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_firma` INT(10) NOT NULL,
	`id_branza_1` INT(10) NULL DEFAULT NULL,
	`id_branza_2` INT(10) NULL DEFAULT NULL,
	`id_branza_3` INT(10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK_firma_branza_firmy` (`id_firma`),
	INDEX `id_branza_2` (`id_branza_2`),
	INDEX `id_branza` (`id_branza_1`, `id_branza_2`, `id_branza_3`),
	CONSTRAINT `firma_branza_ibfk_1` FOREIGN KEY (`id_firma`) REFERENCES `firmy` (`id`),
	CONSTRAINT `firma_branza_ibfk_2` FOREIGN KEY (`id_branza_2`) REFERENCES `branze` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
[SQL] pobierz, plaintext 

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

[HTML] pobierz, plaintext 
<table name="UnknownTable">
	<row>
		<id>1</id>
		<select_type>SIMPLE</select_type>
		<table>firmabranza</table>
		<type>ref</type>
		<possible_keys>FK_firma_branza_firmy,id_branza</possible_keys>
		<key>id_branza</key>
		<key_len>5</key_len>
		<ref>const</ref>
		<rows>129168</rows>
		<Extra>Using where; Using temporary; Using filesort</Extra>
	</row>
	<row>
		<id>1</id>
		<select_type>SIMPLE</select_type>
		<table>firma</table>
		<type>ref</type>
		<possible_keys>PRIMARY,status_priorytet</possible_keys>
		<key>PRIMARY</key>
		<key_len>4</key_len>
		<ref>okf.firmabranza.id_firma</ref>
		<rows>1</rows>
		<Extra>Using where</Extra>
	</row>
</table>
[HTML] pobierz, plaintext 

Czy ktos pomoze w probie ustawienia odpowiednich indeksow zeby to dzialanie zoptymalizowac ?

[alegorn]

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 
SELECT 
	`firma`.id 
FROM 
	`firma_branza` AS `firmabranza`
	JOIN `firmy` AS `firma`  ON (`firma`.`id` = `firmabranza`.`id_firma` AND `status` =1) 
WHERE 
	`id_branza_1` = 455
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]

Witam.

[SQL] pobierz, plaintext 
SELECT * FROM firmy PROCEDURE ANALYSE(100000);
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 
SELECT 
    firma.id
FROM
    firmy AS firma USE INDEX(id_branza)
        LEFT JOIN
    firma_branza AS firmabranza ON (firma.id = firmabranza.id_firma)
WHERE
    id_branza_1 = '455' AND STATUS = '1'
ORDER BY priorytet DESC
LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext 

Ten post edytował rocktech.pl 20.04.2012, 10:13:05

[kiciafu]

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 (IMG:style_emoticons/default/smile.gif)

[alegorn]

@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]

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 
SELECT f.id FROM firma_branza AS fb
LEFT JOIN firmy AS f ON fb.id_firma = f.id
WHERE fb.id_branza_1 = 455 AND f.`status`
ORDER BY f.priorytet DESC
LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext 

a Twoje które powinno ciutkę szybciej zadziałać:

[SQL] pobierz, plaintext 
SELECT f.id FROM firmy AS f
LEFT JOIN firma_branza AS fb ON f.id = fb.id_firma
WHERE f.`status` AND fb.id_branza_1 = 455
ORDER BY f.priorytet DESC
LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext 

Ten post edytował zegarek84 20.04.2012, 10:34:34

[redeemer]

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]

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 
SELECT `firma`.id FROM `firmy` AS `firma` 
STRAIGHT_JOIN `firma_branza` AS `firmabranza`
WHERE `firma`.`id` = `firmabranza`.`id_firma`
   AND `id_branza_1` = '455'
   AND `status` = '1' 
ORDER BY `priorytet` DESC 
LIMIT 20 OFFSET 0
[SQL] pobierz, plaintext 

[gpi]

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 
SELECT 
	`firma`.id 
FROM 
	`firma_branza` AS `firmabranza`
	JOIN `firmy` AS `firma`  ON (`firma`.`id` = `firmabranza`.`id_firma` AND `status` =1) 
WHERE 
	`id_branza_1` = 455
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.

Witam.

[SQL] pobierz, plaintext 
SELECT * FROM firmy PROCEDURE ANALYSE(100000);
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 
SELECT 
    firma.id
FROM
    firmy AS firma USE INDEX(id_branza)
        LEFT JOIN
    firma_branza AS firmabranza ON (firma.id = firmabranza.id_firma)
WHERE
    id_branza_1 = '455' AND STATUS = '1'
ORDER BY priorytet DESC
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"

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 (IMG:style_emoticons/default/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.

@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]

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]

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

Tutaj mam najlepszy rezultat. Musze poprzerabiac moje zapytania w tym kierunku.
Dzieki wielkie.

[Pilsener]

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 
    SELECT `firma`.id FROM `firmy` AS `firma`
    WHERE `firma`.id IN (SELECT id_firma FROM firmabranza WHERE id_branza_1=455 AND STATUS=1)
    ORDER BY `priorytet` DESC
    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]

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 
    SELECT `firma`.id FROM `firmy` AS `firma`
    WHERE `firma`.id IN (SELECT id_firma FROM firmabranza WHERE id_branza_1=455 AND STATUS=1)
    ORDER BY `priorytet` DESC
    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 
 SELECT `firma`.id FROM `firmy` AS `firma`
 WHERE `firma`.id IN (SELECT id_firma FROM firma_branza WHERE id_branza_1=455 AND id_branza_2=548)  AND STATUS=1
 ORDER BY `priorytet` DESC
 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]

[SQL] pobierz, plaintext 
SELECT f.id FROM 
  (SELECT fb.id_firma FROM firma_branza AS fb WHERE fb.id_branza_1 = 455 AND fb.id_branza_2=548) AS fb
LEFT JOIN firmy AS f ON fb.id_firma = f.id
WHERE f.`status`
ORDER BY f.priorytet DESC
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 
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...