| Autor | Zpráva | ||
|---|---|---|---|
| ovcopes Profil |
Zdravím všechny uprostřed pracovního dne :-)
Při optimalizování MySQL dotazů na databázi se mně stává, že při prvním dotazu na databázi dotaz trvá kolem 10 sekund. Veškeré další dotazy jsou pak ale do pul vteřiny. Dotazy testuju na apachu v phpMyadminovy. Nevíte prosím Vás někdo čím to může být, případně jak to odstranit. Díky za odpověd |
||
| Kajman_ Profil * |
#2 · Zasláno: 14. 5. 2008, 09:34:48
Pokud to je nějaký náročnější dotaz, tak třeba při dalším stejném dotazu použije mysql např. cache nebo nějaké další optimalizace vyplívající z předešlého dotazu. Ta varianta s cache by se dala vyloučit dotazem
SELECT SQL_NO_CACHE sloupecky... |
||
| ovcopes Profil |
#3 · Zasláno: 14. 5. 2008, 12:19:12
je to hodne slozitej dotaz
SELECT SP.nazev,L.prijmeni,L.jmeno,TIME_FORMAT(H.do, '%k:%i') AS do,TIME_FORMAT(H.od, '%k:%i') AS od,H.den,Z.typzapisu, SUM(case when ((KT.kdy between '2008-02-25' and '2008-03-02' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't1', SUM(case when ((KT.kdy between '2008-03-03' and '2008-03-09' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't2', SUM(case when ((KT.kdy between '2008-03-10' and '2008-03-16' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't3', SUM(case when ((KT.kdy between '2008-03-17' and '2008-03-23' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't4', SUM(case when ((KT.kdy between '2008-03-24' and '2008-03-30' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't5', SUM(case when ((KT.kdy between '2008-03-31' and '2008-04-06' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't6', SUM(case when ((KT.kdy between '2008-04-07' and '2008-04-13' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't7', SUM(case when ((KT.kdy between '2008-04-14' and '2008-04-20' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't8', SUM(case when ((KT.kdy between '2008-04-21' and '2008-04-27' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't9', SUM(case when ((KT.kdy between '2008-04-28' and '2008-05-04' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't10', SUM(case when ((KT.kdy between '2008-05-05' and '2008-05-11' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't11', SUM(case when ((KT.kdy between '2008-05-12' and '2008-05-18' ) AND KT.kdo=Z.rc AND KT.co=Z.hid) then 1 END) as 't12', H.vyucujici,L.rc,H.hid FROM zapsani Z,hodiny H,sporty SP,sportoviste S,lide L,konto_transakce KT WHERE Z.hid=H.hid AND H.kodsportu=SP.kodsportu AND H.kde=S.id_loc AND L.rc=Z.rc AND S.id_loc='36' GROUP BY Z.rc,Z.hid ORDER BY H.den,H.od,L.prijmeni Kdyz si necham treba vypsat jen prvnich 60, tak to trva dlouho, a kdyz mam strankovanej seznam a kliknu na dalsi stranu, tak to je hned. A pritom je to vlastne jinej dotaz.. Takze to teda jako zdrzuje to nahravani do cache? nebo je problem v selektu? |
||
| nightfish Profil |
#4 · Zasláno: 14. 5. 2008, 12:47:09
ovcopes
Kdyz si necham treba vypsat jen prvnich 60, tak to trva dlouho, a kdyz mam strankovanej seznam a kliknu na dalsi stranu, tak to je hned. A pritom je to vlastne jinej dotaz.. jiný dotaz to není, protože je tam order by, takže se to stejně celé musí seřadit a pak až se provede ten limit... takže do cache se uloží dotaz bez limitu Takze to teda jako zdrzuje to nahravani do cache? nebo je problem v selektu? jak jsou nastaveny indexy? (explain select ...) ideální by zřejmě bylo mít indexy alespoň na KT.kdy, KT.kdo, KT.co, Z.rc, Z.hid, H.hid, H.kodsportu, SP.kodsportu, H.kde, S.id_loc, L.rc, Z.rc, H.den, H.od |
||
| Kajman_ Profil * |
#5 · Zasláno: 14. 5. 2008, 13:08:14
Takze to teda jako zdrzuje to nahravani do cache? nebo je problem v selektu?
Problém je v selectu... když zkusíte to SELECT SQL_NO_CACHE SP.nazev,... tak by to mělo trvat vždy dlouho (nebudou se využívat předešlá získaná data). Takže se ponořte do výstupu explain. Vylaďte indexy, popřemýšlejte, jestli podmínku KT.kdo=Z.rc AND KT.co=Z.hid nemůžete dát rovnou spojení (např. left join) a ještě zvažte, zda to nepůjde jinak než sum(case). |
||
| ovcopes Profil |
#6 · Zasláno: 14. 5. 2008, 22:03:18
na KT.kdy, KT.kdo, KT.co index je dat asi nemuzu, protoze se do tehle tabulky zaznamenavaji furt nove zaznamy(asi 500 za hodinu) a skoro na vsech ostatnich uz index je...
ten select je udelanej tak, aby mne vytvoril z tech 3 informaci (KT.kdy, KT.kdo, KT.co) tabulku 12*x, kde je bud 1 nebo null. Netusim, jak jinym zpusobem takovou tabulku vygenerovat Diky za rady, ponořim se do explain |
||
| TSD Profil |
#7 · Zasláno: 14. 5. 2008, 22:15:08
ovcopes
Jen se zeptám. Co až tam těch týdnů budeš vyhodnocovat 30? Nebo třeba 300? Zvážil bych překopání do toho tvaru, že tam přidáš GROUP BY (nějakzískattýdenzdatumu(KT.kdy)) |
||
| ovcopes Profil |
#8 · Zasláno: 14. 5. 2008, 22:25:38
těch týdnů bude vždycky 12, max +-1
Můžu přidát další GROUP BY, kde už jeden je? |
||
| ovcopes Profil |
#9 · Zasláno: 14. 5. 2008, 22:33:29
Zvážil bych překopání do toho tvaru, že tam přidáš GROUP BY (nějakzískattýdenzdatumu(KT.kdy))
v tabulce "zapsani" slouzi jako primarni index kombinace Z.rc,Z.hid, nějak mně furt nedochází, jak jste to myslel s tim GROUP BY (nějakzískattýdenzdatumu(KT.kdy)) |
||
| TSD Profil |
#10 · Zasláno: 14. 5. 2008, 23:16:37
nějak mně furt nedochází
Nevím, nejsem SQL specialista a ta moje úvaha byla značně nejasná. Jestli jsem to správně pochopil, tak ten dotaz získává hodnoty 't1' až 't12' jako počty výskytů hodnoty KT.kdy v několika týdnech po sobě. Řešil jsem něco hodně vzdáleně podobného. V podstatě šlo o to, že jsem měl záznamy něčeho a chtěl vypsat jejich počty po měsících. Řešení se odvíjelo to, že jsem z časů získal měsíc a podle něj ty záznamy groupoval. |
||
| ovcopes Profil |
#11 · Zasláno: 15. 5. 2008, 01:05:28
v mém případě každý řádek z tabulky 'zapsani' vytvori 1 radek vysledne tabulky a podle toho jestli existuje zaznam v 'konto_transakce' se vytvori v jednotlivych chliveccich 1 nebo null. V podstate se jedna o evidenční seznam
|
||
| Kajman_ Profil * |
#12 · Zasláno: 15. 5. 2008, 09:49:09
Zkusil bych použít ten left join na omezení konto_transakci...
SELECT SP.nazev,L.prijmeni,L.jmeno,TIME_FORMAT(H.do, '%k:%i') AS do,TIME_FORMAT(H.od, '%k:%i') AS od,H.den,Z.typzapisu, SUM(KT.kdy between '2008-02-25' and '2008-03-02') as 't1', SUM(KT.kdy between '2008-03-03' and '2008-03-09') as 't2', SUM(KT.kdy between '2008-03-10' and '2008-03-16') as 't3', SUM(KT.kdy between '2008-03-17' and '2008-03-23') as 't4', SUM(KT.kdy between '2008-03-24' and '2008-03-30') as 't5', SUM(KT.kdy between '2008-03-31' and '2008-04-06') as 't6', SUM(KT.kdy between '2008-04-07' and '2008-04-13') as 't7', SUM(KT.kdy between '2008-04-14' and '2008-04-20') as 't8', SUM(KT.kdy between '2008-04-21' and '2008-04-27') as 't9', SUM(KT.kdy between '2008-04-28' and '2008-05-04') as 't10', SUM(KT.kdy between '2008-05-05' and '2008-05-11') as 't11', SUM(KT.kdy between '2008-05-12' and '2008-05-18') as 't12', H.vyucujici,L.rc,H.hid FROM hodiny H,sporty SP,sportoviste S,lide L,zapsani Z left join konto_transakce KT on KT.kdo=Z.rc AND KT.co=Z.hid and KT.kdy between '2008-02-25' and '2008-05-18' WHERE Z.hid=H.hid AND H.kodsportu=SP.kodsportu AND H.kde=S.id_loc AND L.rc=Z.rc AND S.id_loc='36' GROUP BY Z.hid,Z.rc ORDER BY H.den,H.od,L.prijmeni A možná pomůže vytvoření těchto šesti indexů. sportoviste - (id_loc) hodiny - (kde,hid,kodsportu,den,od) zapsani - (hid,rc) lide - (rc,prijemni) konto_transakce - (co,kdo,kdy) sporty - (kodsportu) |
||
| ovcopes Profil |
#13 · Zasláno: 15. 5. 2008, 15:43:38 · Upravil/a: ovcopes
Zdravím, tak jsem to testoval, a prinasi to zlepseni rychlosti z 10 na 8s - pri prvnim nacteni, a pri dalsim je to rychlejsi 10x
indexy jsem nastavil jak jste mi poradil idselect_type table type possible_keys key key_len ref rows Extra 1 SIMPLE S const PRIMARY PRIMARY 4 const 1 Using index; Using temporary; Using filesort 1 SIMPLE H ref PRIMARY,kde,kodsportu kde 5 const 19 Using where 1 SIMPLE SP eq_ref PRIMARY PRIMARY 22 cesa.H.kodsportu 1 Using where 1 SIMPLE Z ref hid hid 4 cesa.H.hid 18 1 SIMPLE KT ref kdo kdo 12 cesa.Z.rc,cesa.Z.hid 2 Using index 1 SIMPLE L eq_ref rc rc 8 cesa.Z.rc 1 Ve vsech tabulka je realne mnozstvi dat - lide(65000), hodiny(600), zapsani(8000),sportoviste(50),sport(50), jenom konto_transakce jen 15. Rozdíl oproti té Vaší a mojí verzi by se ještě zvětšil , kdyby těch zaznamu v teto tabulce bylo vic. odhad kolem 50000 Zlepseni je citit, ale furt 8 vterin... |
||
| Kajman_ Profil * |
#14 · Zasláno: 15. 5. 2008, 19:20:04
indexy jsem nastavil jak jste mi poradil
Určitě? V tom přehledu to nevypadá, že by tam byly ty více sloupcové indexy. |
||
| ovcopes Profil |
#15 · Zasláno: 15. 5. 2008, 22:54:20 · Upravil/a: ovcopes
tak snad už ted
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE S const PRIMARY PRIMARY 4 const 1 Using index; Using temporary; Using filesort 1 SIMPLE H ref PRIMARY,kodsportu,kde_hid_ks_den_od kde_hid_ks_den_od 5 const 19 Using where 1 SIMPLE SP eq_ref PRIMARY PRIMARY 22 cesa.H.kodsportu 1 Using where 1 SIMPLE Z ref hid_rc hid_rc 4 cesa.H.hid 85 1 SIMPLE L ref rc_prijmeni rc_prijmeni 8 cesa.Z.rc 1 1 SIMPLE KT ref co_kdo_kdy co_kdo_kdy 12 cesa.Z.hid,cesa.L.rc 2 Using index tak to slo o dalsi 2 vterinky dolu. Ono asi taky zalezi na poradi v tom multiindexu a pak v tom selectu ze? A to SELECT SQL_NO_CACHE sloupecky... nepomaha, A ani restart MySQL... |
||
| Kajman_ Profil * |
#16 · Zasláno: 15. 5. 2008, 23:21:21
Ono asi taky zalezi na poradi v tom multiindexu a pak v tom selectu ze?
Ano, zkuste si s tím pohrát. Ale to pořadí v selectu není tak důležité jak pořadí, ve kterém to zpracovává db. A to SELECT SQL_NO_CACHE sloupecky... nepomaha Takže druhý takovýto select je bleskový? To máte asi pokaženou verzi mysql. |
||
| ovcopes Profil |
#17 · Zasláno: 15. 5. 2008, 23:46:53
Takže druhý takovýto select je bleskový? To máte asi pokaženou verzi mysql.
Zkouším to na localhostu, XAMPP 1.6.5, + Apache 2.2.6 + MySQL 5.0.51 + PHP 5.2.5. Prvni SELECT ted trva kolem 6sekund, a veskere dalsi (treba zmena AND S.id_loc='600' ) do 0.1s abych zjistil rychlost prvni dotazu, tak musim dycky restartovat |
||
|
Časová prodleva: 16 let
|
|||
0