Dotaz: MySQL velký SELECT

[Petr]

Dobrý den,

když spustím tento dotaz, běží několik minut (pak jsem ho ukončil) a ostatní uživatele nemohou s databází vůbec pracovat:

SELECT Records.ClientNr, Clients.Name AS Nazev, LEFT(Clients.VSB,2) AS Obch, COUNT(DISTINCT Records.RecordNr) AS Spisy, DATE_FORMAT(Recdate,'%Y%m') AS DatM, MAX(CASE WHEN VChrontable.Kuerzel = 'V23' THEN VChrontable.Datum END) AS DatSml, SUM(CASE WHEN Chrontable.Kuerzel LIKE 'RG%' AND Chrontable.Kuerzel <> 'RGKČX' OR Chrontable.Kuerzel LIKE 'Z00%' THEN Chrontable.Betrag ELSE 0 END) AS Suma From Records, Chrontable, Clients, VChrontable Where Records.RecordNr = Chrontable.RecordNr And Records.ClientNr = Clients.ClientNr And Clients.ClientNr = VChrontable.ClientNr GROUP BY Records.ClientNr, DatM

Je špatně napsaný ten SELECT, nebo špatný hardware nebo nastavení MySQL? Recordset je otevřen na straně serveru. Verze MySQL 4.1.13, linux SUSE.

tabulka Records má 65 000 záznamů, Chrontable 880 000, Clients 12 000, VChrontable 95 000 záznamů.

Odpovědi

[Heron]

Chtělo by to upravit formátování.

Co na to řekne EXPLAIN? Používají se indexy nebo fullscan? Jaký je typ tabulek? Jaký je to HW? Tabulky jsou poměrně malé, neměl by být důvod pro takovéto časy.

[Petr]

typ tabulek je MyISAM. HW nevím, je to nějaký nový server.
EXPLAIN:

-------+-----+------+--------------+----------+-------+-----------------+-----+--------------+
|select|table|type  |possible_keys |key       |key_len|ref              |rows |Extra         |
-------+-----+------+--------------+----------+-------+-----------------+-----+--------------+
|SIMPLE|Recor|ALL   |PRIMARY,CL_IDX|[NULL]    | [NULL]|[NULL]           |65621|Using filesort|
|SIMPLE|Clien|eq_ref|PRIMARY       |PRIMARY   |      4|Records.ClientNr |    1|              |
|SIMPLE|VChro|ref   |Client_idx    |Client_idx|      5|Clients.ClientNr |    7|Using where   |
|SIMPLE|Chron|ref   |Record_idx    |Record_idx|      5|Records.RecordNr |   13|Using where   |
+------+------------+--------------+----------+-------+-----------------+-----+--------------+

[happy barney]

odporčam vám ísť na to postupne, najprv select jeden stĺpec, potom pridať ďaľší, až to jeden zblbne úplne (napr v poradí 1, 2, 3, 5, 4, 6, 7)

[kafa]

Nechybí vám v tabulce Records index na sloupci RecordNr, podle kterého spojujete?

[Petr]

Nechybí. Všechny sloupce RecordNr a ClientNr jsou indexovány. Index je i na Chrontable.Kuerzel, Clients.Name, ale není na VChrontable.Kuerzel

[kafa]

Jo, už to vidím. Výběr z Chrontable ani Clients není nijak omezen, takže Records se musí číst celý. Problém ovšem vidím v tom, že v tabulce Records (evidentně M:N) se ClentNr bude mnohokrát opakovat a pro každý výskyt se musí znovu provést výběr podřízených řádků v tabulce Vchrontable. Já bych nejprve v jednom selectu vytvořil temporary tabulku ze spojení Clients a Vchrontable, (GROUP BY ClientsNr - víc než 12000 řádků by neměla) a pak bych Records spojoval pouze s Chrontable a touto temporary tabulkou). Jsem přesvědčen, že výsledek by byl mnohokrát rychleji.

[kafa]

Nebo ho alespoň přinutit, aby nezačínal spojení tabulkou M:N. Vyzkoušel bych SELECT STRAIGHT_JOIN ... a seznam tabulek za FROM seřadil v pořadí Clients, Vchrontable, Records, Chrontable. Pak se Vchrontable bude prohledávat pro každého použitého klienta pouze jednou.

[Filip Jirsák]

Trochu upravím formátování:

SELECT
  Records.ClientNr,
  Clients.Name AS Nazev,
  LEFT(Clients.VSB,2) AS Obch,
  COUNT(DISTINCT Records.RecordNr) AS Spisy,
  DATE_FORMAT(Recdate,'%Y%m') AS DatM,
  MAX(CASE WHEN VChrontable.Kuerzel = 'V23' THEN VChrontable.Datum END) AS DatSml,
  SUM(CASE WHEN Chrontable.Kuerzel LIKE 'RG%' AND Chrontable.Kuerzel <> 'RGKČX'
    OR Chrontable.Kuerzel LIKE 'Z00%' THEN Chrontable.Betrag ELSE 0 END) AS Suma
FROM
  Records,
  Chrontable,
  Clients,
  VChrontable
WHERE
  Records.RecordNr = Chrontable.RecordNr
  AND Records.ClientNr = Clients.ClientNr
  AND Clients.ClientNr = VChrontable.ClientNr
GROUP BY Records.ClientNr, DatM

Jinak mi není jasné, co tím dotazem vlastně chcete zjistit. Ty podmínky v agregačních funkcích vypadají divně (proč nejsou ve WHERE?), používat za SELECTem mimo agregační funkce sloupce, které nejsou v GROUP BY, by vám v jiné databázi než MySQL neprošlo (a bez pohledu do dokumentace nedokážu určit, jak moc náhodně bude MySQL vybírat hodnoty pro ty sloupečky). Teď mne ještě praštilo do očí – pokud je DatM v GROUP BY opravdu ten formátovaný řetězec, pak chudák databáze – na to nemůže použít žádný index. A jestli se nepletu tohle by vám opět u jiných databází neprošlo.

[happy barney]

ad DatM v group by: to by prešlo, alternatívne je možné písať group by 1, 5 (podľa prvého a piateho stĺpca)

mne osobne sa nepáči:
- spomínané chýbajúce stĺpce v group by
- distinct v count
- or v SUM - chýbajúce else v MAX

podmienky v agregačných funkciách majú zmysel, aj keď podivný (návrh databázy smrdí nemčinou, tam sa podivnosti očakávať dajú)

[Petr]

Pokusím se popsat tabulky :

Records(RecordNr long,ClientNr long, Recdate date)
Chrontable(Kuerzel varchar,Betrag double)
Clients(ClientNr long, VSB varchar)
VChrontable(ClientNr long, Kuerzel varchar, Datum date)

a co chci zjistit:
ClientNr, počet RecordNr, k nim součet RG (v mém dotazu AS Suma), první dva znaky z Clients.VSB,
VChrontable.datum pro Kuerzel = 'V23' (pokud existuje) seskupeno podle ClientNr 
a podle měsíce z Records.Recdate (yyyymm)

nebo jinými slovy počet Records.RecordNr, 
součet Chrontable.Betrag kde RecordNr=Records.RecordNr a kde Kuerzel LIKE 'RG%' ... za každý měsíc
 dle Records.Recdate a k tomu VChrontable.Datum kde je Kuerzel = 'V23' a k tomu Clients.VSB

[Petr]

když do WHERE přidám Records.ClientNr < 25, tak to proběhne za cca 20s a vrátí 140 řádků

[Dejv]

Co zase me prastilo do oci je spojeni tabulek. Nejsem si jisty, ale mam pocit, ze tebou pouzite spojeni spoji zaznamy kazdy s kazdym a teprve tohle spojeni filtruje podle where. Podle jmen sloupcu (a tvojeho where) odhaduji, ze by to mohlo byt

FROM
  Records
  join Clients on Clients.ClientNr=Records.ClientNr
  join VChrontable on VChrontable.ClientNr=Records.ClientNr
  join Chrontable on Chrontable.Kuerzel=VChrontable.Kuerzel

A urcite bych se pokusil vyhnout spojeni pres varchar polozku.

Pokud placam nesmysly, prosim zkusenejsi, aby me opravili

Dejv

[ZAH]

Skoro bych se připojil osobně bych zkusil subselecty. (Doufám že nekecám a MySQL to umí)

SELECT
  Records.ClientNr,
  select Clients.Name from clients where Records.ClientNr = Clients.ClientNr AS Nazev,
  LEFT(Clients.VSB,2) AS Obch,
  select ....
  DATE_FORMAT(Recdate,'%Y%m') AS DatM,
  select .... 
  select ........
FROM  
     Records
GROUP BY 
      Records.ClientNr, DatM

[kafa]

Mýlíte se, oba způsoby spojení se provádějí naprosto identicky. Jde jen o jiný formát zápisu, který oddělí spojovací podmínky od ostatních, aby byl přehlednější pro člověka a ten méně chyboval. Databázi je to jedno - oba styly vyhodnocuje shodně.

[Dejv]

Hm, ja to tusil, ze zase placam blbosti Diky za opravu

Dejv

[Dejv]

Ale jeste jeden tip pridam. Nechybi ve spojeni tabulek (teda ve where) jeste pole Chrontable.Kuerzel=VChrontable.Kuerzel?

Dejv

[Petr]

Ne. Tyto tabulky nemaji byt navzajem propojeny.

[Dejv]

A jo, blbne jsem se dival, promin. V popisu tabulek nemas uvedeny sloupec Chrontable.RecordNr a ja si nevsiml, ze to podle neho mas spojene ve where.

Priste zkusim lip cist

Dejv

[Kyosuke]

"Ty podmínky v agregačních funkcích vypadají divně (proč nejsou ve WHERE?), používat za SELECTem mimo agregační funkce sloupce, které nejsou v GROUP BY, by vám v jiné databázi než MySQL neprošlo"

MySQL něco takového opravdu sežere a ani nepípne? Není to "poněkud" proti SQL normě?

[Ladicek]

Jo. Něco podobného se mi kdysi povedlo s InterBase 6, když jsem to pak chtěl pustit na Firebirdu, tak na mne křičel Ale už si nepamatuju přesně, o co šlo.

[Kyosuke]

No když seskupuješ, tak musíš sloupce výsledku buď agregovat, nebo bez agregací používat sloupce s hodnotama, který jsou pro každý agregovaný řádek prokazatelně jednoznačný...třeba právě ty hodnoty, podle kterých se seskupuje. (Možná že v SQL normě jsou jen ty agregovaný sloupce a sloupce z GROUP BY, možná má databáze povolený inferovat další jednoznačný buňky - tím už si nejsem jistej.)

Což Ti asi vysvětlovat nemusím, ale jsem si skoro jistý, že se Ti stalo právě tohle - taky se mi občas stane, že na to zapomenu, ale nikdy by mě nenapadlo, že může existovat ("relační", zde v uvozovkách ) databáze, která na to neupozorní. BTW, pokud náhodou skutečně potřebuješ skupinu hodnot čistě do výsledku, ve Firebirdu 2.1 přibyla agregační fce LIST( [ {ALL | DISTINCT } ] <value_expression> [ ',' <delimiter_value> ] ), která umí "atomizovat" sloupce do seznamu prezentovatelného uživateli. Vrací to řetězec. Asi záleží na klientské straně, ale chvílema se to může hodit, třeba v ISQL.

[cronin]

Skus http://forum.dbsvet.cz/.

[Tomas]

Formátování by opravdu hodně pomohlo na čitelnosti.

Zkusil bych následující kroky ke zjištění co dělá problém:

• Vyhodit COUNT( DISTINCT ...)
• Vyhodit agregace a vyzkoušet jak dlouho poběží samotné joiny a zda se vrátí přibližně správný počet záznamů.
• Ve frázi GROUP BY místo DatM dát RecDate a vyhodit ze selectu sloupec DatM