Podkręcanie GROUP_CONCAT Opcje

[TomASS]

Witam serdecznie.

Mam w bazie danych realację jeden do wielu. Mam tabelę transporty oraz miejsca. Do jednego transportu może być przypisanych kilka miejsc.

Aby całość trasy (wszystkie miejsca danego transportu) wyciąnąć w zapytaniu daję GROUP CONCAT:

[SQL] pobierz, plaintext 
SELECT T.*, (SELECT GROUP_CONCAT(DISTINCT M.Miejscowosc ORDER BY Rodzaj DESC SEPARATOR '-') FROM m_miejsca AS M WHERE M.ID_transport=T.ID_t AND M.Miejsce!='del') FROM m_transporty AS T;
[SQL] pobierz, plaintext 

Niestety daje mi to czas 8 sekund dla 100 rekordow. Po usunieciu GROUP_CONCAT daje mi czas 0,03s.
Co próbowałem:
1. Uproszczenie GROUP_CONCAT: (SELECT GROUP_CONCAT(M.Miejscowosc SEPARATOR '-') FROM m_miejsca AS M WHERE M.ID_transport=T.ID_t ) - nic nie daje (minimalne przyspieszenie - 1,5%)
2. Przeniesienie funkcjonalności do "STORED FUNCTION" - także nic


Czy ktoś ma jakiś pomysł?

[sazian]

daj explain
pewnie wystarczy indeksy poprawić

[TomASS]

Zapomniałem powiedzieć - indeksy działają. Rozmiar danych nie jest zatrważający:
transporty 14 000 rekordów
miejsca 32 000 rekordów

[sazian]

a masz pewność że indeksy są prawidłowe ?
dla pewności pokarz wynik explain

[TomASS]

EXPLAIN pokazuje:

id = 2
select_type = DEPENDENT SUBQUERY
table = M
type = ALL
possible_keys = ID_transport,ID_transport_2
key = NULL
key_len = NULL
ref = NULL
rows = 28735
Extra = Using where

Podzapytanie:

[SQL] pobierz, plaintext 
(SELECT GROUP_CONCAT(DISTINCT M.Miejscowosc ORDER BY Rodzaj DESC SEPARATOR '-') FROM m_miejsca AS M WHERE M.ID_transport=T.ID_t AND M.Miejsce!='del')
[SQL] pobierz, plaintext 

Indeksy dla tabeli m_miejsca:
Pierwszy: ID_transport
Drugi: ID_transport + Miejsce

[mortus]

Nie ma się co dziwić, że zapytanie działa tak długo, ponieważ sprawdza setki kombinacji, zanim trafi na odpowiedni zbiór pogrupowanych miejscowości. Jest po prostu trochę źle napisane:

[SQL] pobierz, plaintext 
SELECT T.*,
  (
    SELECT GROUP_CONCAT(M.Miejscowosc SEPARATOR '-')
    FROM m_miejsca AS M
    WHERE M.Miejsce != 'del'
    GROUP BY M.ID_transport
    HAVING M.ID_Transport = T.ID_t
  ) AS Miejscowosci FROM m_transporty AS T;
[SQL] pobierz, plaintext 

Nie wiem czemu ma służyć sortowanie, ale dopisać nie będzie trudno.

Grupujemy i łączymy tylko te rekordy, których M.ID_transport jest zgodne z T.ID_t.

PS1: Usunąłem część odpowiedzi, bo brzmiała dwuznacznie, tzn. można ją było dwuznacznie zrozumieć.

PS2: Teraz tak patrzę i widzę, że różnica jest niewielka. Zastanawiam się tylko, po co Ci DISTINCT w podzapytaniu i dlaczego te tabele są połączone relacją jeden do wielu. Wydawać by się mogło, że do tego potrzebne są trzy tabele i relacja wiele do wielu, ale to zależy od założeń aplikacji.

Ten post edytował mortus 2.04.2013, 20:33:48

[TomASS]

Zmieniłem - teraz działa 16 sekund :/

Dokładnie to wygląda teraz tak:

[SQL] pobierz, plaintext 
SELECT DISTINCT 
  T.Numer, T.Numer_zleceniodawcy, Zlecniodawca.Nazwa, T.ID_przewoznik, 
  Przewoznik.Nazwa, 1, Odleglosc, ROUND(T.Cena_jednostkowa_netto_od_sprzedawcy*T.Ilosc,2), 
  T.Waluta_zlecenia, ROUND(T.Cena_netto_dla_przewoznika,2), T.Waluta_dla_przewoznika, 
  T.Towar, CONCAT_WS(' ',Kierowca.Nazwisko, Kierowca.Imie), Kierowca_spedycja, 
  Ciezarowka.Numer_ciagnika, Ciezarowka_spedycja, Faktura.Numer, DATE(T.Data_realizacjiPoczatek), 
  DATE(T.Data_realizacjiKoniec), Rodzaj_transportu, T.ID_t, Oddzial.Nazwa, ' ', NumerFakturyOdPrzewoznika, T.FakturaObca, 
  (SELECT GROUP_CONCAT(M.Miejscowosc ORDER BY Rodzaj DESC SEPARATOR '-')
      FROM m_miejsca AS M
      WHERE M.Miejsce != 'del' AND M.ID_Transport = T.ID_t 
      GROUP BY M.ID_transport)  
FROM 
  m_transporty AS T LEFT JOIN 
  m_kontrahenci AS Zlecniodawca ON (Zlecniodawca.ID_k=T.ID_platnik) LEFT JOIN 
  m_kontrahenci AS Przewoznik ON (Przewoznik.ID_k=T.ID_przewoznik) LEFT JOIN 
  m_kierowcy AS Kierowca ON (Kierowca.ID_k=T.ID_kierowca) LEFT JOIN 
  m_ciezarowki AS Ciezarowka ON (Ciezarowka.ID_c=T.ID_ciezarowka) LEFT JOIN 
  f_faktury AS Faktura ON (Faktura.ID_f=T.ID_faktura) LEFT JOIN 
  m_oddzialy AS Oddzial ON (Oddzial.ID_o=T.ID_oddzial) 
WHERE 
  T.Miejsce!='del' AND 
  1 AND 
  WEEK(T.Data_realizacjiPoczatek)='2' AND 
  YEAR(T.Data_realizacjiPoczatek)='2013' 
ORDER BY T.Numer ASC
[SQL] pobierz, plaintext 

Przeniosłem "M.ID_Transport = T.ID_t" z HAVING do WHERE i jest lepiej - 4sekundy, ale nadal kiepsko :/

Ten post edytował TomASS 2.04.2013, 20:41:32

[mmmmmmm]

spróbuj tak:

[SQL] pobierz, plaintext 
SELECT DISTINCT
    T.Numer,
    T.Numer_zleceniodawcy,
    Zlecniodawca.Nazwa,
    T.ID_przewoznik,
    Przewoznik.Nazwa,
    1,
    Odleglosc,
    ROUND(T.Cena_jednostkowa_netto_od_sprzedawcy * T.Ilosc,
            2),
    T.Waluta_zlecenia,
    ROUND(T.Cena_netto_dla_przewoznika, 2),
    T.Waluta_dla_przewoznika,
    T.Towar,
    CONCAT_WS(' ', Kierowca.Nazwisko, Kierowca.Imie),
    Kierowca_spedycja,
    Ciezarowka.Numer_ciagnika,
    Ciezarowka_spedycja,
    Faktura.Numer,
    DATE(T.Data_realizacjiPoczatek),
    DATE(T.Data_realizacjiKoniec),
    Rodzaj_transportu,
    T.ID_t,
    Oddzial.Nazwa,
    ' ',
    NumerFakturyOdPrzewoznika,
    T.FakturaObca,
    x.group_miejscowosc
FROM
    m_transporty AS T
        LEFT JOIN
    m_kontrahenci AS Zlecniodawca ON (Zlecniodawca.ID_k = T.ID_platnik)
        LEFT JOIN
    m_kontrahenci AS Przewoznik ON (Przewoznik.ID_k = T.ID_przewoznik)
        LEFT JOIN
    m_kierowcy AS Kierowca ON (Kierowca.ID_k = T.ID_kierowca)
        LEFT JOIN
    m_ciezarowki AS Ciezarowka ON (Ciezarowka.ID_c = T.ID_ciezarowka)
        LEFT JOIN
    f_faktury AS Faktura ON (Faktura.ID_f = T.ID_faktura)
        LEFT JOIN
    m_oddzialy AS Oddzial ON (Oddzial.ID_o = T.ID_oddzial)
        LEFT JOIN
    (SELECT 
        ID_Transport,
            GROUP_CONCAT(M.Miejscowosc
                ORDER BY Rodzaj DESC
                SEPARATOR '-') group_miejscowosc
    FROM
        m_miejsca AS M
    WHERE
        M.Miejsce != 'del'
    GROUP BY ID_Transport) x ON (x.ID_Transport = T.ID_t)
WHERE
    T.Miejsce != 'del' 
    AND WEEK(T.Data_realizacjiPoczatek) = 2
    AND YEAR(T.Data_realizacjiPoczatek) = 2013
ORDER BY 
	T.Numer ASC
[SQL] pobierz, plaintext 

[TomASS]

Nic Widzę, że wywalenie DISTINCT pomaga (do 0,4s). Jakoś to sobie przemodeluje aby DISTINCT (po pierwszym SELECT) nie występował. Dziękuję wszystkim za pomoc.

[mortus]

Od razu trzeba było powiedzieć, że takiego kolosa testujesz, bo takie wyniki przy pierwszym podanym przez Ciebie zapytaniu i 100 rekordach w moich oczach były po prostu niemożliwe. Z DISTINCT to jest tak, że w przypadku posortowanego zbioru danych (ORDER BY) tworzona jest tabela tymczasowa i to z niej są wybierane rekordy bez duplikatów... stąd tak długi czas wykonywania zapytania. Z założenia DISTINCT ma służyć do wybrania rekordów bez powtórzeń, ale to samo można osiągnąć grupując powtarzające się rekordy według odpowiednich kolumn. DISTINCT to po prostu "specjalny przypadek GROUP BY". Zastanawia mnie tylko skąd założenie, że przy tak dużej ilości danych (gdzie każde T.ID_t powinno być unikalne) pojawią się duplikaty.