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.

[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