MySQL Dlouhé načítání zdánlivě jednoduchého vnořeného dotazu

19. 10. 2020 12:41:41

Ahoj všem,

mohl bych vás požádat - co dělám špatně.

Chci zobrazit v tabulce statistiku, kolik která firma obsahuje třeba emailových kontaktů. Tabulka bude stránkovat po 500 záznamech

TABLE1 - seznam firem (cca 25tis radku)

ID,NAZEV,ADRESA,DATUM...

TABLE2 - seznam emailu (cca 200tis radku)

ID,ID_FIRMY,EMAIL,DATUM

Mám SQL dotaz

Vypíšou se správně 3 sloupce s názvem firmy a počtem emailů, ale dotaz s limitem 0,500 trvá přes 65s.

19. 10. 2020 12:41:41

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466403

uzivatel1

(7 hodnocení)

19. 10. 2020 12:49:17

Používej spíš JOIN, vnořené dotazy jsou výrazně náročnější na čas.

19. 10. 2020 12:49:17

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466402

(20 hodnocení)

19. 10. 2020 12:50:32

v tmohle pripade se spousti enormni mnozstvi subqueries, to proste chvili trva... doporucuju tohle pocitat jen jednou za cas, mit na to primo sloupecek u firmy a proste to jendou za cas prepocitat nebo to prepocitavat prubezne pri pridani/odebrani emailu...

i ten join bude zbytecne pomalej, protoze musi joinnout a zgroupovat hodne zaznamu, zalezi jak casto se to bude kde volat...

19. 10. 2020 12:50:32

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466401

TomasX

(4 hodnocení)

19. 10. 2020 13:27:19

pro každý řádek se spouští další subselect, tj. 25 tis subselektů, mysql tohle neumí optimalizovat, proto to je tak dlouhé, explain ti to řekne. Použití join se správná rada a řešení.

25 tis záznamů není velký počet a indexy nemusí být tak potřeba, ale jakmmile narozste počet záznamů ještě víc, zkontroluj, jestli na sloupcích id a id_firmy máš indexy. Stejně tak daleko lepších výsledků dostaneš, jestli je tabulka uložen jako innodb a používáš cizí klíče.

19. 10. 2020 13:27:19

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466400

kamil_h

19. 10. 2020 14:09:04

Dekuji vsem, upravil jsem sql dotaz do realu

SELECT tab_firmy.id,tab_firmy.firma,count(tab_kontakty.id_firmy) as emaily

FROM tab_firmy

JOIN tab_kontakty ON tab_kontakty.id_firmy=tab_firmy.id

group by tab_firmy.id, tab_firmy.firma

a vse se podstatne zrychlilo, 500 radku cca 3s. Na tom se uz pracovat da. Tabulka je innodb

---------- Příspěvek doplněn 19.10.2020 v 13:10 ----------

Jeste upresnim, ze zpracovani vsech zaznamu (cca 25tis) trvalo mene, nez jsem napocital do 5.

19. 10. 2020 14:09:04

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466399

TomasX

(4 hodnocení)

19. 10. 2020 14:13:38

nauč se trochu pracovat s explain, hodně ti pomůže v ladění těhle dotazů.

19. 10. 2020 14:13:38

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466398

kamil_h

19. 10. 2020 19:15:22

Tak jsem procetl spoustu rad jak pouzivat explain, jak analisovat tabulku (procedure analyse()) a muj projekt s praci na DB to hodne pozdvihlo. Nyni si dovoluji zobrazovat na strance takova data, ktera jsem drive diky dlouhemu nacitani radeji neresil.

Zacal jsem tedy resit nejakou statistiku s prehledem a dovolil si pridat jeste jeden JOIN, ale zrejme mi unika nekde nejaka vazba, protoze to ukazuje misto poctu emailu a poctu poznamek v dane firme nejaka nesmyslna cisla a v obou sloupcich uplne stejna. Nejsou to ani soucty, ani souciny. Kdyz v selectu pouzivam pouze jeden JOIN (at emaily nebo poznamky), jde to krasne.

Neumeli byste me nakopnout, jak pridat dalsi JOIN?

struktura TABLE3 - seznam poznamek (cca 30tis radku)

ID,ID_FIRMY,POZNAMKA,DATUM

19. 10. 2020 19:15:22

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466397

TomasX

(4 hodnocení)

19. 10. 2020 19:52:43

Výsledek toho joinu je kartézský součin tabulek t2 a t3, tj. každý řádek s každým pro každé ID. Nemůžeš použít agregační funkce (count) s různou bází. V tvém sql chybí tabulka t1, předpokládám, že to je edit_crm_firmy.

Řešením je tohle spojit postupně. Případně, když vykresluješ jen pár záznamů na stránku (desítky), můžeš použít subselect.

Napsal bych to asi takhle:

Předpokládám, že pro každé t1.id je pouze jeden řádek t1.firma a není potřeba přes to groupovat. Jak to bude výkonné netuší, nové databáze to dávají dobře, starší verze mysql s tímhle dotazem měli problémy, tak si to musíš vyzkoušet. Bohužel se ty výpočty musí dělat pro všechny firmy, šlo by to ale optimalizovat s dalším joinem nebo where in podmínkou, kdy si nejprve vybereš firmy, které chceš vykreslit a použiješ je v podmínce. Jo, kdykoliv použiješ limit, je vhodné přidat i order nebo mohou být výsledky dost nepředvídatelné.

Pro vysvětlení. Nejprve si samostatně napočítám vazby id a počet emailů, id a počet poznámek, tyhle dvě tabulky spojím do výsledku. Tím neměním hodnost agregační funkce tím, že připojím další 1:N či N:M tabulku.

Novější databáze poté nabízí i tzv. analytické funkce, které tohle umí dělat efektivně, to ale MySQL umí až od 8 a Mariadb od 10.2.

19. 10. 2020 19:52:43

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466396

kamil_h

19. 10. 2020 22:53:17

Uff, moc dekuji, tuhle konstrukci bych teda nedal. Kouknu na to, zatim jsem to upravil do skutecneho pojmenovani a phpmyadmin rika, ze nezna sloupec edit_crm_firmy.id, ale na to snad prijdu. zkusim zabalit do zpetnych uvozovek. Dotaz tedy vypada takto

19. 10. 2020 22:53:17

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466395

TomasX

(4 hodnocení)

19. 10. 2020 23:23:30

edit_crm_firmy tabulku jsem přejměnoval na "t1", viz řádek za from, proto jí to nemůže najít, odstraň to "t1" a bude to asi fungovat.

Left join je kvůli tomu, aby tabulka s poznámkami mohla být prázdná.

19. 10. 2020 23:23:30

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466394

kamil_h

19. 10. 2020 23:38:26

Tak jsem na to konecne prisel, tento SELECT trva 1,5s a vybira bez limitu celou tabulku, cili >25k zaznamu.

Dekuji ti TomasX za nakopnuti, dej vedet chces-li, jak ti mohu poslat "$" na pivo .-) Vazim si casu jinych ve prospech jim cizich.

19. 10. 2020 23:38:26

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466393

TomasX

(4 hodnocení)

19. 10. 2020 23:45:32

Dobrá práce, že jsi to nějak zkrotit. To je asi přijatelný čas. Pokud bys nejprve vybral id firem a pak je dal do where in podmínky, čas by šel násobně dolu, nemuselo by se počítat vše.

Díky, ale nejsem tady kvůli penězům.

19. 10. 2020 23:45:32

https://webtrh.cz/diskuse/mysql-dlouhe-nacitani-zdanlive-jednoducheho-vnoreneho-dotazu/#reply1466392

Pro odpověď se přihlašte.

Přihlásit