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 ?

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

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