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
pmir13
post
Post #2





Grupa: Zarejestrowani
Postów: 282
Pomógł: 89
Dołączył: 12.04.2011

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


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:

  1. SELECT `firma`.id FROM `firmy` AS `firma`
  2. STRAIGHT_JOIN `firma_branza` AS `firmabranza`
  3. WHERE `firma`.`id` = `firmabranza`.`id_firma`
  4. AND `id_branza_1` = '455'
  5. AND `status` = '1'
  6. ORDER BY `priorytet` DESC
  7. LIMIT 20 OFFSET 0


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(pmir13 @ 20.04.2012, 22:10:35 ) *
  1. SELECT `firma`.id FROM `firmy` AS `firma`
  2. STRAIGHT_JOIN `firma_branza` AS `firmabranza`
  3. WHERE `firma`.`id` = `firmabranza`.`id_firma`
  4. AND `id_branza_1` = '455'
  5. AND `status` = '1'
  6. ORDER BY `priorytet` DESC
  7. LIMIT 20 OFFSET 0


Tutaj mam najlepszy rezultat. Musze poprzerabiac moje zapytania w tym kierunku.
Dzieki wielkie.
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: 17.10.2025 - 12:32