"skomplikowane" zapytanie, Zbyt mała wydajność Opcje

[sheldon]

Jak w temacie.
Mam problem z zapytaniem do bazy w mysql. Trwa ono nieskończenie długo (lub ~ 154sekundy).

[SQL] pobierz, plaintext 
-- 
-- Struktura tabeli dla  `londoner_users`
-- 
 
CREATE TABLE `londoner_users` (
  `user_id` int(5) NOT NULL AUTO_INCREMENT,
  `user_login` varchar(255) NOT NULL DEFAULT '',
  `user_pass` varchar(255) NOT NULL DEFAULT '',
  `user_name` varchar(255) NOT NULL DEFAULT '',
  `user_surname` varchar(255) NOT NULL DEFAULT '',
  `user_address` varchar(255) NOT NULL DEFAULT '',
  `user_city` varchar(255) NOT NULL DEFAULT '',
  `user_postcode` varchar(10) NOT NULL DEFAULT '',
  `user_province` int(5) NOT NULL DEFAULT '0',
  `user_gg` int(9) NOT NULL DEFAULT '0',
  `user_email` varchar(255) NOT NULL DEFAULT '',
  `user_email1` varchar(255) NOT NULL DEFAULT '',
  `user_phone` varchar(30) NOT NULL DEFAULT '',
  `user_cellphone` varchar(30) NOT NULL DEFAULT '',
  `user_fax` varchar(30) NOT NULL DEFAULT '',
  `user_certs` text NOT NULL,
  `user_info` text NOT NULL,
  `user_notices` text NOT NULL,
  `user_sworn` enum('yes','no') NOT NULL DEFAULT 'no',
  `user_busy` enum('yes','no') NOT NULL DEFAULT 'no',
  `user_paytype` int(5) NOT NULL DEFAULT '0',
  `user_date` int(15) NOT NULL DEFAULT '0',
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=1775 ;
 
-- --------------------------------------------------------
 
-- 
-- Struktura tabeli dla  `londoner_users_combinations`
-- 
 
CREATE TABLE `londoner_users_combinations` (
  `user_combination_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) NOT NULL DEFAULT '0',
  `combination_id` int(5) NOT NULL DEFAULT '0',
  `combination_prize` float NOT NULL DEFAULT '0',
  PRIMARY KEY  (`user_combination_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=12523 ;
 
-- --------------------------------------------------------
 
-- 
-- Struktura tabeli dla  `londoner_users_spheres`
-- 
 
CREATE TABLE `londoner_users_spheres` (
  `user_sphere_id` int(5) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) NOT NULL DEFAULT '0',
  `sphere_id` varchar(5) NOT NULL DEFAULT '',
  UNIQUE KEY `user_sphere_id` (`user_sphere_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=91384 ;
 
-- --------------------------------------------------------
 
-- 
-- Struktura tabeli dla  `londoner_users_types`
-- 
 
CREATE TABLE `londoner_users_types` (
  `user_type_id` int(5) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) NOT NULL DEFAULT '0',
  `type_id` int(5) NOT NULL DEFAULT '0',
  `type_prize` float NOT NULL DEFAULT '0',
  UNIQUE KEY `user_type_id` (`user_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=14104 ;
[SQL] pobierz, plaintext 

Są takie 4 tabele. Napisałem wyszukiwarkę, która pięknie działała na małej ilości użytkowników. Przy ponad 1700 - zaczyna się problem - zapytanie trwa zbyt długo, baardzo obciąża serwer.

Wygląda ono następująco:
(przykładowo)

[SQL] pobierz, plaintext 
SELECT londoner_users.user_name AS name, londoner_users.user_surname AS surname, londoner_users.user_gg AS gg, londoner_users.user_phone AS phone, londoner_users.user_cellphone AS cellphone, londoner_users.user_email AS email, londoner_users.user_city AS city, londoner_users.user_postcode AS postcode, londoner_users.user_address AS address, londoner_provinces.province_name AS province, londoner_users.user_sworn AS sworn, londoner_users.user_id AS user_id 
FROM londoner_users_combinations LEFT JOIN londoner_users ON londoner_users.user_id = londoner_users_combinations.user_id 
LEFT JOIN londoner_users_spheres ON londoner_users_spheres.user_id = londoner_users.user_id 
LEFT JOIN londoner_users_types ON londoner_users_types.user_id = londoner_users.user_id 
LEFT JOIN londoner_provinces ON londoner_users.user_province = londoner_provinces.province_id 
WHERE londoner_users_spheres.sphere_id = '4' AND londoner_users_combinations.combination_id = '847' AND londoner_users.user_province = '15' AND londoner_users_types.type_id = '4' 
GROUP BY londoner_users.user_id
[SQL] pobierz, plaintext 

Problemem są ogromne tabele - londoner_users_spheres - ma ponad 68.600 rekordów oraz londoner_users_types - ponad 7000.

Może macie jakieś pomysły? :-(

[kicaj]

Czy przy tej wyszukiwarce potrzebne jest az tyle table, a moze sprobujesz podzapytania?

[sheldon]

Jak proponowałbyś to zrobić?

[DeyV]

napisz, do czego służy ta baza, oraz jakie dane chcesz pobierać tym zapytaniem.
Może pomyślimy o optymalizacji struktury lub samego zapytania.

[sheldon]

Baza ta służy do pobrania listy tłumaczy spełniających dane kryteria:
1. wykonujących daną "kombinację językową" - combination_id
2. mieszkają w danym województwie - province_id
3. tłumaczą w danej specjalizacji - sphere_id
4. wykonują dany typ tłumaczenia - type_id.

W tabeli londoner_users znajdują się tłumacze oraz ich dane.
W tabeli londoner_users_combinations znajdują się ID kombinacji, ID użytkownika oraz kwota, za jaką dane tłumaczenie wykonuje (w tym przypadku możną ją pominąć)
W tabeli londoner_users_types znajdują się userzy (user_id) i odpowiadające im typy tłumaczeń (type_id) oraz za ile to wykonują (bez znaczenia również)
W tabeli londoner_users_spheres są userzy (user_id) jak i przyporządkowane im specjalizacje (sphere_id).

Jeśli coś przeoczyłem - przepraszam, dajcie cynk - zaraz dopiszę.
Już w tym momencie dziękuje za chęć pomocy :-)

[DeyV]

Zacznijmy od optymalizacji zapytania.

W zapytaniu tym nie ma żadnych argumentów uzasadniających użycie GROUP BY
W związku z tym proponuję użyć klauzuli DISTINCT user_id , która jest znacznie szybsza.

Co dalej? Jeśli możesz - podaj jeszcze strukturę jednej brakującej tabeli.
Dodatkowo - jeśli możesz - udostępnij gdzieś plik z przykładowymi danymi w tych tabelach (ale nie wrzucaj ich na forum ) tak bym mógł to sobie potestować na prawdziwych, logicznych związkach.

[sheldon]

[SQL] pobierz, plaintext 
CREATE TABLE `londoner_provinces` (
  `province_id` int(5) NOT NULL AUTO_INCREMENT,
  `province_name` varchar(255) NOT NULL DEFAULT '',
  UNIQUE KEY `province_id` (`province_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=19 ;
[SQL] pobierz, plaintext 

Oto brakująca tabelka :-)

Zaraz podam przykładowe dane :-)

[DeyV]

Ok. No to bawimy się dalej.

Teraz struktura bazy.
Na wszystkie kolumny, będące kluczami zewnętrznymi zakładamy indexy. (klucze zewnętrzne, to np. londoner_users_spheres.user_id )
Zasade tą stosujemy na WSZYSTKICH połączeniach pomiędzy tabelami.


Edited:

Mały test, już z danymi

Bez żadnych indexów: 0.4449 sekundy
Z wszystkimi indexami: 0.0122 sekundy

Teraz DISTINCT zamiast GROUP: 0.0112 sekundy (o dziwo niewiele to dało. hmm )

[sheldon]

http://www.oc-forum.org/baza.zip - tutaj są wszelkie dane.
(a indexy już zakładam :-) )
//
wrzuciłem poprawioną wersję danych z bazy, w poprzednim pliku się dublowały niektóre rekordy (3-krotnie), pod powyższym linkiem jest już dobra wersja.


EDIT:
O kurcze, pododawałem indexy, znalazłem też inny błąd: pole sphere_id w londoner_users_spheres ... było varcharem (wielka pomyłka, już poprawiłem) - cyka wszystko niesamowicie pięknie teraz z tego co widzę. Będę jeszcze testował
Wielkie dzięki za pomoc!

Ten post edytował sheldon 25.12.2005, 22:05:11

[DeyV]

I dlatego wole Postgresa... On by nie pozwolił na taki związek

Pozdrawiam

[sheldon]

Ja jeszcze raz bardzo dziękuję za pomoc, a przy okazji się czegoś nauczyłem - czegoś, co powinienem wiedzieć od dawna :-)
Jeszcze raz pozdrawiam!