Pouze tento týden sleva až 80 % na e-learning týkající se C# .NET. Zároveň využij akci až 30 % zdarma při nákupu e-learningu - Více informací.
Hledáme nové posily do ITnetwork týmu. Podívej se na volné pozice a přidej se do nejagilnější firmy na trhu - Více informací.

Diskuze: pomalý sql dotaz + join v joinu

Aktivity
Avatar
Zbyněk Chatt:12. ledna 14:10

Zdravím,

měl bych dvě prosby. Mám následující SQL dotaz, prvně je dosti pomalý (cca 2 sekundy), šlo by to nějak poladit? Lépe už jsem to neuměl.

A za druhé, potřebuji tam, co ej GROUP_CONCAT, aby to nějak joinulo i jména kurýrů jako v joinu nad tím, a vložilo k IDéčkám v tom concatu, abych nemusel mít zvlášť další dotaz jen na jména kurýrů ...

Díky ...

$result3 = $db2->query("SELECT * FROM dt_mot_max_time");
$sql_del = array();
while ($r3 = $result3->fetch_array()){

     for ($i1=0;$i1<=1;$i1++){
         $timed = $r3['dt_mot_max_time'];
         if ($i1==1) $timed *= 2;
         $sql_del[] = "
             WHEN
             delivery.delivery_type_id='".$r3['delivery_type_id']."' AND
             delivery.mot_id='".$r3['mot_id']."' AND
             delivery.delivery_return='".$i1."'
             THEN DATE_ADD(delivery_date_created, INTERVAL ".$timed." MINUTE)
         ";
     }
}
 $sql_del_final = implode(" ",$sql_del);

 $result4 = $db2->query("
         SELECT *, @couriers = '',
         (CASE
             ".$sql_del_final."
         END)  AS enddate
         FROM delivery
     LEFT JOIN user ON delivery.user_id=user.user_id
     LEFT JOIN mot ON delivery.mot_id=mot.mot_id
     LEFT JOIN delivery_type
            ON delivery.delivery_type_id=delivery_type.delivery_type_id
     LEFT JOIN (
             SELECT courier_name AS courier_name, courier_id
             FROM courier) courier1
             ON delivery.courier1_id=courier1.courier_id
     LEFT JOIN (
             SELECT courier_name AS courier_name2, courier_id
             FROM courier) courier2
             ON delivery.courier2_id=courier2.courier_id
     LEFT JOIN (
             SELECT GROUP_CONCAT( IFNULL(courier_id,0)  SEPARATOR ',') AS couriers,
             MAX(id) AS id8, delivery_id, id AS id7
                         FROM courier_interest
                         GROUP BY delivery_id
                         ORDER by id DESC
                         LIMIT 200) interest
                         ON interest.delivery_id=delivery.delivery_id

     WHERE delivery_date_created<='$date1'
         ORDER BY
             enddate DESC, delivery.delivery_id DESC
         LIMIT 30");

Zkusil jsem: Hledal jsem adekvátní náhradu za group_concat, ale nic nenalézám, ten je asi ze všeho nejpomalejší. Dal jsem tam nakonec limit 200 odzadu, ale určitě nastane situace, kdy to nepokryje vše, co potřebuji vytáhnout. I bez joinu s concatem je to pomalé, cca 2 sekundy. S concatem bez limitu pak cca 5 sekund.

Chci docílit: Zrychlení scriptu, načtení kurýrů do group_concatu, např. ve formátu courier_id|cou­rier_name,cou­rier_id|couri­er_name

 
Odpovědět
12. ledna 14:10
Avatar
Zbyněk Chatt:12. ledna 14:34

PS napadlo mě, ke každému SELECTu v JOINu nějak dát WHERE a teď vyjmenovat všechny delivery_id ze základního SELECTu. Ale to nevím, jak udělat, ale mohlo by to pomoct? A ještě pomohlo by místo vybrání všeho (*) vyjmenovat pouze sloupce, které potřebuji?

 
Nahoru Odpovědět
12. ledna 14:34
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:12. ledna 15:16

Na mne, treba, musis pomalu. Ty si tu placnes dotaz, ktery neco dela a nevysvetlis co to ma delat :)

Verim, ze kdyz zrusis ty posledni LEFT JOIN, kde mas ty SELECTy, tak by ten dotaz mel probehnout rychle, je to tak?
Pokud to tak je, tak to znamena, ze to ostatni ti to nejak brzdi.


LEFT JOIN (
        SELECT courier_name AS courier_name, courier_id
        FROM courier) courier1
        ON delivery.courier1_id=courier1.courier_id

Proc tam vybiras vsechny courier_name a pak to stejne joinujes podle courier_id, ktera tam ma kazdy radek jen 1x? Tak to LEFT JOINi preci primo na tu tabulku courier, ne?

LEFT JOIN courier courier1
         ON delivery.courier1_id=courier1.courier_id
LEFT JOIN courier courier2
         ON delivery.courier2_id=courier2.courier_id

A ten group snad ani nejdu studovat. Rekni ,co je v tech tabulkach na vsrupu v tech sloupcich, co mas v tom dotazu. A jak ma vypadat vystupni tabulka?
Ono, totiz, kdyz to vytahnes pres SELECT a pak pripojis, tak prijdes v podstate o indexy, protoze mas uplne jinou tabulku.
To si mozna zkus ten sql dotaz dat zvlast, kdyz mu das nejake pevne cislo, kolik to zebere casu

interest.delivery_id=delivery.delivery_id
interest.delivery_id=5153

Kdyz si vemes ten pudovni dotaz, tak, pokud tam bude vzdy 200 tech kuryru, tak v tom vnejsim mas limit 30 radku. To je jakoby 30x200, 6000 radku. To samo o sobe je trochu problem. Takze, uz je dost blbe, ze mas 200 tech pridanych kuryrovych interest. To je, jako bych vypisoval 30 knizek a mel k nim pridat 200 autoru. Takove hodne zvlastni, proto bych rad vedel, co tam je.

A neslo by treba ten SELECT napsat nahoru mezi sloupce? Ale, asi ne, tusim tam jde dat 1 sloupec. A nevim, zda by nebyla rychlejsi ta tva verze.

         SELECT *, @couriers = '',
         (CASE
             ".$sql_del_final."
         END)  AS enddate,
             (
             SELECT GROUP_CONCAT( IFNULL(courier_id,0)  SEPARATOR ',') AS couriers,
             MAX(id) AS id8, delivery_id, id AS id7
                         FROM courier_interest
                         WHERE interest.delivery_id=delivery.delivery_id -- tady bych pridal jeste filtr na id
                         GROUP BY delivery_id
                         ORDER by id DESC
                         LIMIT 200
           ) AS jmena

         FROM delivery
...

A nebo ty udaje stahnout dalsi sql dotazem?

A nebo, nemuzes tam pridat tu podminku na id. Ja jsem spis takovy experimentattor, takze, mozna rikam hloupost :) Ale, mam pocit, ze pri kazdem pokusu o join tam stahuje zas celou tabulku a pak z ni vybere jen ten 1 radek.

WHERE interest.delivery_id=delivery.delivery_id -- tady bych pridal jeste filtr na id
 
Nahoru Odpovědět
12. ledna 15:16
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:12. ledna 15:28

Jako, urcite by to slo udelat tak, ze si udelas temp tabulku, kde si pripravis prave ten group. Ale to sem zatim nikdy nepouzil, tak by sis to musel vygooglovat. Vim, ze tam jsou nejake 2 moznosti, vytvorit to jako temp nebo ulozit do promene.
Kdyz to mas takhle v dotazu, tak on prave pokazde tu tabulku dela nejspis znova a znova. A pak zalezi na tvem nastaveni sql, zda to kesuje nebo ne.
Nebo, pokud takovy select delas casto, tak bych si natvrdo generoval ty data pri insertu do sqlka do extra tabulky.
https://www.sqlshack.com/…-sql-server/

 
Nahoru Odpovědět
12. ledna 15:28
Avatar
Odpovídá na Peter Mlich
Zbyněk Chatt:12. ledna 15:56

Verim, ze kdyz zrusis ty posledni LEFT JOIN, kde mas ty SELECTy, tak by ten dotaz mel probehnout rychle, je to tak?

Když odstraním ten poslední JOIN, tak to pořád chroustá asi dvě sekundy, leč je to o něco málo rychlejší, než s tím JOINem. Problém bude tudíž primárně jinde. Jak říkáš, viděl bych to na hromadu těch JOINů, což třeba mohu vyřešit u těch kurýrů, že udělám na jména kurýrů jeden SQL dotaz zvlášť. Zkusím a ještě dám vědět.

Výstup je tady: https://www.racek-kuryr.cz/admin4/plik.php

V tom courier_interest jsou žádosti o zásilky, kde jsou akorát sloupce id, id zásilky a id kurýra. Pokud se o jednu zásilku hlásí více kurýrů, pak je tam více řádků s jedním id zásilky, ale pokaždé s jiným id kurýra. A pokud tomu tak je, tak na výstupu to pak rozparsuju do html selectu. Tam potřebuju ID kurýrů, což mám díky tomu concatu, ale nemám jejich jména. A nechtěl jsem na to dělat extra další sql dotaz.To mě nenapadá, jak to tam přidat do toho.

WHERE interest.delivery_id=delivery.delivery_id

nelze použít: Unknown column 'delivery.deli­very_id' in 'where clause'
což jsem ani nemusel zkoušet, protože vím, že dovnitř JOINu nejde dostat vnější data, nebo nevím jak.

Snad jsem zodpověděl vše :)

 
Nahoru Odpovědět
12. ledna 15:56
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:12. ledna 16:52

Ja myslel, ze zrusis vsechny ty joiny. Bez nich by to mel byt obycejny select v radu mikrosekund. Pak ho muze brzdit jen to case.
Takze, pokud to i pak jde pomalu, tak nemas nad tabulkou nastavene indexy pro vsechny sloupce, ktere mas u WHERE nebo join ON.

Editováno 12. ledna 16:55
 
Nahoru Odpovědět
12. ledna 16:52
Avatar
Odpovídá na Peter Mlich
Zbyněk Chatt:12. ledna 17:14

OK, zrušil jsem všechny JOINy a o polovinu rychlejší, ale pořád to je dlouhé .... Ten case je teda obrovský, je to 4 prostředky krát 4 tarify krát 2 (zpáteční a nezpáteční), tudíž to je 32 x WHEN ... jestli by to mohlo být ono ... ale to jinak udělat asi nepůjde, protože tam načítám k jakému prostředku a jakému tarifu jsou různé časy a pak je nutné to vypsat seřazené podle zbývajícího času. Tohle jediné asi nevyřeším.

 
Nahoru Odpovědět
12. ledna 17:14
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:12. ledna 21:21

No, a kdyz odstranis ten cas?
Protoze, zatim to vypada, ze nemas nastavene indexy. 2s dotazy je tak 100% pomalejsi nez normal. A jediny duvod, ktery mne napada, pokud to nedelaji ty joiny a case, tak prave to, ze nemas nastavene indexy. a mebo mas pres 10.000.000 zaznamu. Coz asi nemas.

 
Nahoru Odpovědět
12. ledna 21:21
Avatar
Zbyněk Chatt:13. ledna 10:14

Super, příčina nalezena! Když odstraním ten CASE, a dokonce i když smažu limit v tom groupu, tak dotaz trvá 0,01 sec. Pokud k tomu přidám ten CASE, tak trvá 2,8 sec. Prosím, je nějaká šance to vyřešit při zachování toho, čeho potřebuju docílit a to řazení dle zbývajícího času, který je třeba ovšem vypočítat?

PS. nesmazal jsem ten query, kterým tvořím ten CASE, jen jsem smazal ten CASE z toho hlavního query.

Tabulka vypadá nějak takto:

Prostředek Tarif  Čas (m)
Auto       Klasik    120
Auto       Expres     60
Auto       Letecky    40
Auto       Ekonomicky240
Moto       Klasik    120
Moto       Expres     60
Moto       Letecky    40
Moto       Ekonomicky240
Pickup     Klasik    150
Pickup     Expres     90
Pickup     Letecky    60
Pickup     Ekonomicky NULL
Dodávka   Klasik    NULL
Dodávka   Expres     NULL
Dodávka   Letecky    NULL
Dodávka   Ekonomicky NULL
 
Nahoru Odpovědět
13. ledna 10:14
Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!
Avatar
Zbyněk Chatt:13. ledna 11:02

Další poznatek. Pokud tam nechám jen jeden WHEN

CASE WHEN delivery.delivery_type_id>0 THEN DATE_ADD(delivery_date_created, INTERVAL 5 MINUTE) END AS enddate

Tak rázem z 0,01 sec dotazu se stane 1,22 sec dotaz. Hm ... Takže i snížení počtu vůbec ničemu nepomůže. Je třeba to udělat úplně jinak a nenapadá mě moc jak.

Možná načíst všechna potřebná data do ARRAY, načíst tabulku s časama zvlášť a posléze to pole seřadit dle potřeby. Ale to se mi jednak moc nechce, jednak to bude vypadat hrozně a bude to zbytečně nepřehledné. Ideální by bylo to vyřešit nějak jinak přímo v té query. Jdu přemýšlet. Sem s nápady pls! :)

Editováno 13. ledna 11:03
 
Nahoru Odpovědět
13. ledna 11:02
Avatar
Zbyněk Chatt:13. ledna 11:19

Nečekaně VYŘEŠENO. Dohledal jsem na nějakém zahraničním fóru. Problém byl tady:

WHERE  delivery_date_created<='$date1'  or delivery_status='1'

A vyřešeno takto:

WHERE (delivery_date_created>='$date1a' AND delivery_date_created<='$date1') or delivery_status='1'

Já nevěděl, že LIMIT nezohledňuje počet načítaných záznamů, prostě bere v potaz všechny, ale vypíše jen ten limit. Je potřeba prostě ve WHERE specifikovat, co vlastně chci ... Juchů :D

No a teď ten druhý dotaz

LEFT JOIN (SELECT GROUP_CONCAT( IFNULL(courier_id,0)  SEPARATOR ',') AS couriers, delivery_id
                                FROM courier_interest
                                GROUP BY delivery_id
                                ORDER by id DESC
                                ) interest
                                ON interest.delivery_id=delivery.delivery_id

Jak do tohohle prosím vměstnám jména kurýrů? Stále jsem bez nápadu.

 
Nahoru Odpovědět
13. ledna 11:19
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:13. ledna 11:36

Nechapu, ceho chces tim case docilit. Co to presne ma udelat a proc? Nemuzes to proste pripojit pres join left?
Jakoze, hlavne netusim, co jsou ty sloupce zac, jaka data v nich jsou a proc a odkud se tam berou. Nebo nejake takove informace. Vysledkem je nejaky cas. To je asi nedine, co zatim chapu :)

$result3 = $db2->query("SELECT * FROM dt_mot_max_time"); // nevidim obsah te tabulky
$sql_del = array();
while ($r3 = $result3->fetch_array()){

     for ($i1=0;$i1<=1;$i1++){
         $timed = $r3['dt_mot_max_time']; // tim paden tohle je pro mne UNDEFINED
         if ($i1==1) $timed *= 2; // UNDEFINED*2
         $sql_del[] = "
             WHEN
//             delivery.delivery_type_id='".$r3['delivery_type_id']."' AND
//             delivery.mot_id='".$r3['mot_id']."' AND
//             delivery.delivery_return='".$i1."'
//             THEN DATE_ADD(delivery_date_created, INTERVAL ".$timed." MINUTE)
             delivery.delivery_type_id='UNDEFINED' AND
             delivery.mot_id='UNDEFINED' AND
             delivery.delivery_return='0 nebo 1' //  muze to byt i neco jineho? co znamena, kdyz je to 0 a co 1?
             THEN DATE_ADD(delivery_date_created, INTERVAL UNDEFINED MINUTE)
         ";
     }
}

A zkus mi podle toho rict, proc to ten clovek dela prave takhle, jestli to je nej reseni nebo by to slo jinak :)

 
Nahoru Odpovědět
13. ledna 11:36
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:13. ledna 11:41

Jak jako jmena kuryru. Ja se teda dost ztracim v tom, jake mas tabulky a co v nich :)
Jaky mas treba aktualne sql dotaz a co to vypisuje a co by to melo vypisovat?

 
Nahoru Odpovědět
13. ledna 11:41
Avatar
Odpovídá na Peter Mlich
Zbyněk Chatt:13. ledna 11:49

Dobře, aktuální verze:

$today = date();
$date1 = date("Y-n-j",strtotime($today." + 1 days"));
$date1a = date("Y-n-j",strtotime($today." - 30 days"));
$started1 = microtime(true);
$result3 = $db2->query("SELECT * FROM dt_mot_max_time");
$sql_del = array();
while ($r3 = $result3->fetch_array()){

     for ($i1=0;$i1<=1;$i1++){
         $timed = $r3['dt_mot_max_time'];
         //if ($i1==1) $timed *= 2;
         $sql_del[] = "
             WHEN
             delivery.delivery_type_id='".$r3['delivery_type_id']."' AND
             delivery.mot_id='".$r3['mot_id']."' AND
             delivery.delivery_return='".$i1."'
             THEN DATE_ADD(delivery_date_created, INTERVAL ".$timed." MINUTE)
         ";
     }
}
 $sql_del_final = implode(" ",$sql_del);

$started = microtime(true);
 $result4 = $db2->query("
         SELECT * ,
         (CASE
             ".$sql_del_final."
         END)  AS enddate
         FROM delivery
     LEFT JOIN (SELECT user_id, user_address_company FROM user) user ON delivery.user_id=user.user_id
     LEFT JOIN mot ON delivery.mot_id=mot.mot_id
     LEFT JOIN delivery_type ON delivery.delivery_type_id=delivery_type.delivery_type_id
     LEFT JOIN (SELECT courier_name AS courier_name, courier_id FROM courier) courier1 ON delivery.courier1_id=courier1.courier_id
     LEFT JOIN (SELECT courier_name AS courier_name2, courier_id FROM courier) courier2 ON delivery.courier2_id=courier2.courier_id
     LEFT JOIN (SELECT GROUP_CONCAT( IFNULL(courier_id,0)  SEPARATOR ',') AS couriers, delivery_id
                         FROM courier_interest
                         GROUP BY delivery_id
                         ORDER by id DESC
                         ) interest
                         ON interest.delivery_id=delivery.delivery_id

     WHERE (delivery_date_created>='$date1a' AND delivery_date_created<='$date1') or delivery_status='1'
         ORDER BY
             enddate DESC, delivery.delivery_id DESC
         LIMIT 30");

Co je v tabulkách, je snad jasné kromě tabulky dt_mot_max_time, kterou jsem vypsal o pár příspěvků výše.

Tak, co má dělat ta první query: vypočítat čas, který zbývá na danou zásilku od času vytvoření. Podle toho pak řadit finální výsledky. V tabulce je 16 záznamů, tím, že přidám zpáteční, viz ten foreach, dostávám se na 32. Ale to je jedno, to už je vyřešeno.

Jaky mas treba aktualne sql dotaz a co to vypisuje a co by to melo vypisovat?

Celé jsem to tedy vypsal hned v úvodu a co to má vypisovat? Zásilky řazené podle zbývajícího času. Firma, ulice od, ulice kam, přidělený kurýr, tarif a prostředek. A pokud to nemá přiřazený žádný kurýr, tam ten group_concat vezme všechny žádosti kurýrů (na jedno delivery_id může být více řádků s různými ID kurýry). a v jedné proměnné to vypíše s rozdělovníkem - čárkou. To potom vyeexploduju do HTML SELECTu a tím se bude přiřazovat kurýr dle výběru dispčera. Ten select se zobrazí na pozici jména kurýra. (žádný přiřazený = volné políčko = bude tam select)

PS. v tabulce courier_interest je pouze delivery_id a courier_id. Není tam jméno. To potřebuju joinout z tabulky courier. Jak to udělám? Chci, by výsledek vypadal takto:
ID|jménoKurýra,ID|jmé­noKurýra,ID|jmé­noKurýra

Editováno 13. ledna 11:52
 
Nahoru Odpovědět
13. ledna 11:49
Avatar
Odpovídá na Peter Mlich
Zbyněk Chatt:13. ledna 12:06

K vysvětlení:

dt_mot_max_time = počet minut, které má tarif (delivery_type_id) a prostředek (mot_id) čas.
Třeba Expres a Auto = dt_mot_max_time = 60. Atp. Viz tabulka o pár příspěvků výše.
if ($i1==1) $timed *= 2; = pokud je zásilka zpáteční, čas se vynásobí 2x.

 
Nahoru Odpovědět
13. ledna 12:06
Avatar
Zbyněk Chatt:13. ledna 12:27

Ještě přidávám výstup pro lepší pochopení :)

 
Nahoru Odpovědět
13. ledna 12:27
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:13. ledna 15:24

kuryr - zasilky
To uz jsem se te ptal, proc to resis selectem a normal joinem? Predpokladam, teda, ze muze byt jen jeden nebo zadny

SELECT
       courier1.name as courier1,
       courier2.name as courier2
FROM ...
    delivery
    LEFT JOIN courier courier1
        ON courier1.courier_id = delivery.courier1_id
    LEFT JOIN courier courier2
        ON courier2.courier_id = delivery.courier2_id

Na hlavni tabulku prilepis ty dve. Ackoliv mi neni uplne jasne, proc v tom prvnim dotazu mas kuryr1 a kuryr2, kdyz v te vysledne tabulce nic takove nemas.

kuryr - volni
Aha, takze nejsou volni kuryri, jen ti, co podali zajem o doruceni zasilky. Tak, to mozna nepujde resit, jak jsem psal.
Pozor, ale, nemuzes to dat do stejneho sloupce jako kuryr jiz vybrany. samozrejme, pri vypisu si to sluc, jak chces.
Muzes zkusit tohle, dej si tam nejake delivery id, ktere tam ma kuryry nula.

delivery (delivery_id, courier1_id, courier2_id) D
courier_interest (delivery_id, courier_id) I
courier (courier_id, ourier_name) C

SELECT
    GROUP_CONCAT( (i.courier_id + '=' + c.courier_name)  SEPARATOR ';') AS couriers_list
FROM
    courier_interest i
        LEFT JOIN courier c
             ON c.courier_id = i.courier_id
WHERE
    i.delivery_id = 123
--    i.delivery_id = d.delivery_id
GROUP BY
    i.delivery_id
 
Nahoru Odpovědět
13. ledna 15:24
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 17 zpráv z 17.