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 ?

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