Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

> Dlugie zapytanie przy JOIN i ORDER BY
gpi
post
Post #1





Grupa: Zarejestrowani
Postów: 11
Pomógł: 0
Dołączył: 29.09.2008

Ostrzeżenie: (0%)
-----


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:
  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


Budowa tabeli firmy:
  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;


Budowa tabeli zlaczeniowej branz:
  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;


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

  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>



Czy ktos pomoze w probie ustawienia odpowiednich indeksow zeby to dzialanie zoptymalizowac ?
Go to the top of the page
+Quote Post
 
Start new topic
Odpowiedzi
alegorn
post
Post #2





Grupa: Zarejestrowani
Postów: 341
Pomógł: 40
Dołączył: 23.06.2009

Ostrzeżenie: (0%)
-----


czas masz z uwagi na to ze zapytanie wymaga tabeli tymczasowej (wymuszone najpewniej przez order by)

z czystej ciekawosci sprawdź (nie testowalem zapisu) :

  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


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

j.
Go to the top of the page
+Quote Post
gpi
post
Post #3





Grupa: Zarejestrowani
Postów: 11
Pomógł: 0
Dołączył: 29.09.2008

Ostrzeżenie: (0%)
-----


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) :

  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


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.

  1. SELECT * FROM firmy PROCEDURE ANALYSE(100000);
  2. SELECT * FROM firma_branza PROCEDURE ANALYSE(100000);


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

Co do samego zapytania.

  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


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

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
Go to the top of the page
+Quote Post

Posty w temacie


Reply to this topicStart new topic
2 Użytkowników czyta ten temat (2 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Aktualny czas: 14.10.2025 - 23:37