Autor | Zpráva | ||
---|---|---|---|
Trska Profil * |
#1 · Zasláno: 11. 4. 2012, 17:00:25
Dobrý den,
na webu mám velkou spoustu SQL dotazů a jsou velmi složité, takže se dlouho vykonávají. Nešlo by je nějak optimalizovat? Zkrátka docílit co nejkratší doby pro zpracování. <?php $registrovanych = mysql_result(mysql_query("SELECT COUNT(*) FROM `accounts`"),0); $zabanovanych = mysql_result(mysql_query("SELECT COUNT(*) FROM `banlist` WHERE `type`!= 2"),0); $mest = mysql_result(mysql_query("SELECT COUNT(*) FROM `towny_towns`"),0); $narodu = mysql_result(mysql_query("SELECT COUNT(*) FROM `towny_nations`"),0); $cas = mysql_result(mysql_query("SELECT SUM(onlinetime) FROM `lb-players`"),0); $nolife = mysql_fetch_row(mysql_query("SELECT playername, onlinetime FROM `lb-players` ORDER BY onlinetime DESC")); $nolifer = $nolife[0]; $noliferCas = floor($nolife[1] / 3600); $hodiny = floor($cas / 3600); $zlataku = mysql_result(mysql_query("SELECT SUM(balance) FROM `iconomy`"),0); $veznu = mysql_result(mysql_query("SELECT COUNT(*) FROM `jail_prisoners`"),0); $bloky = mysql_fetch_row(mysql_query("SELECT SUM(created), SUM(destroyed) FROM ( ( SELECT playerid, count(type) AS created, 0 AS destroyed FROM `lb-minecraftie` WHERE type > 0 AND type != replaced ) UNION ( SELECT playerid, 0 AS created, count(replaced) AS destroyed FROM `lb-minecraftie` WHERE replaced > 0 AND type != replaced ) UNION ( SELECT playerid, count(type) AS created, 0 AS destroyed FROM `lb-minecraftie_nether` WHERE type > 0 AND type != replaced ) UNION ( SELECT playerid, 0 AS created, count(replaced) AS destroyed FROM `lb-minecraftie_nether` WHERE replaced > 0 AND type != replaced ) UNION ( SELECT playerid, count(type) AS created, 0 AS destroyed FROM `lb-creative` WHERE type > 0 AND type != replaced ) UNION ( SELECT playerid, 0 AS created, count(replaced) AS destroyed FROM `lb-creative` WHERE replaced > 0 AND type != replaced ) ) as t WHERE playerid != 7")); $znicenychbloku = $bloky[1]; $postavenychbloku = $bloky[0]; $blokyHraciCre = mysql_fetch_row(mysql_query("SELECT playername, MAX(created) FROM ( ( SELECT playerid, count(type) AS created, 0 AS destroyed FROM `lb-minecraftie` WHERE type > 0 AND type != replaced GROUP BY playerid ) UNION ( SELECT playerid, 0 AS created, count(replaced) AS destroyed FROM `lb-minecraftie` WHERE replaced > 0 AND type != replaced GROUP BY playerid ) ) as t INNER JOIN `lb-players` USING (playerid) where playername NOT LIKE 'WaterFLow' GROUP BY playername ORDER BY SUM(created) DESC")); $nejStavitelPocet = $blokyHraciCre[1]; $nejStavitel = $blokyHraciCre[0]; $blokyHraciDest = mysql_fetch_row(mysql_query("SELECT playername, MAX(destroyed) FROM ( ( SELECT playerid, count(type) AS created, 0 AS destroyed FROM `lb-minecraftie` WHERE type > 0 AND type != replaced GROUP BY playerid ) UNION ( SELECT playerid, 0 AS created, count(replaced) AS destroyed FROM `lb-minecraftie` WHERE replaced > 0 AND type != replaced GROUP BY playerid ) ) as t INNER JOIN `lb-players` USING (playerid) where playername NOT LIKE 'WaterFLow' GROUP BY playername ORDER BY SUM(created) DESC")); $nejNicitelPocet = $blokyHraciDest[1]; $nejNicitel = $blokyHraciDest[0]; $pocetVIP = mysql_result(mysql_query("SELECT count(DISTINCT child) FROM `permissions_inheritance` WHERE type=1 and (parent = \"VIP\" or parent = \"Gladiator\" or parent = \"Premium\")"),0); $pismenka = mysql_fetch_row(mysql_query("SELECT MAX(pismen), playername, SUM(pismen) FROM ( ( SELECT playername, SUM(CHAR_LENGTH(message)) as pismen FROM `lb-chat` JOIN `lb-players` USING(playerid) where playerid != 1214 group by playername ) ) as t ")); $pocetPismenek = $pismenka[2]; $nejPisalek = $pismenka[1]; $nejPisalekPocet = $pismenka[0]; $zpravy = mysql_fetch_row(mysql_query("SELECT MAX(zprav), playername, SUM(zprav) FROM ( ( SELECT playername, count(id) as zprav FROM `lb-chat` JOIN `lb-players` USING(playerid) where playerid != 1214 group by playername ) ) as t")); $pocetZprav = $zpravy[2]; $nejUkecanost = $zpravy[1]; $nejUkecanostPocet = $zpravy[0]; $pocetTruhly = mysql_result(mysql_query("SELECT SUM(pocet) FROM ( ( SELECT count(id) AS pocet FROM `lb-minecraftie-chest` ) UNION ( SELECT count(id) AS pocet FROM `lb-minecraftie_nether-chest` ) UNION ( SELECT count(id) AS pocet FROM `lb-creative-chest` ) ) as t"),0); $pocetCedulky = mysql_result(mysql_query("SELECT SUM(pocet) FROM ( ( SELECT count(id) AS pocet FROM `lb-minecraftie-sign` ) UNION ( SELECT count(id) AS pocet FROM `lb-minecraftie_nether-sign` ) UNION ( SELECT count(id) AS pocet FROM `lb-creative-sign` ) ) as t"),0); $pocetCedulkyPismena = mysql_result(mysql_query("SELECT SUM(delka) FROM ( ( SELECT SUM(CHAR_LENGTH(signtext)) AS delka FROM `lb-minecraftie-sign` ) UNION ( SELECT SUM(CHAR_LENGTH(signtext)) AS delka FROM `lb-minecraftie_nether-sign` ) UNION ( SELECT SUM(CHAR_LENGTH(signtext)) AS delka FROM `lb-creative-sign` ) ) as t"),0); $pocetVrazd = mysql_result(mysql_query("SELECT SUM(pocet) FROM ( ( SELECT count(id) AS pocet FROM `lb-minecraftie-kills` ) UNION ( SELECT count(id) AS pocet FROM `lb-minecraftie_nether-kills` ) UNION ( SELECT count(id) AS pocet FROM `lb-creative-kills` ) ) as t"),0); $zabijak = mysql_fetch_row(mysql_query("SELECT playername, MAX(nej) FROM ( ( SELECT playername,count(killer) AS nej FROM `lb-minecraftie-kills` JOIN `lb-players` ON killer=playerid group by playername ) UNION ( SELECT playername,count(killer) AS nej FROM `lb-minecraftie_nether-kills` JOIN `lb-players` ON killer=playerid group by playername ) ) as t")); $nejZabijak = $zabijak[0]; $nejZabijakPocet = $zabijak[1]; $objet = mysql_fetch_row(mysql_query("SELECT playername, MAX(nej) FROM ( ( SELECT playername,count(victim) AS nej FROM `lb-minecraftie-kills` JOIN `lb-players` ON victim=playerid group by playername ) UNION ( SELECT playername,count(victim) AS nej FROM `lb-minecraftie_nether-kills` JOIN `lb-players` ON victim=playerid group by playername ) ) as t")); $nejObjet = $objet[0]; $nejObjetPocet = $objet[1]; $kdr = mysql_fetch_row(mysql_query("SELECT playername, MAX(nej) FROM ( ( SELECT playername,count(killer)/count(victim) AS nej FROM `lb-minecraftie-kills` JOIN `lb-players` ON killer=playerid group by playername ) UNION ( SELECT playername,count(killer)/count(victim) AS nej FROM `lb-minecraftie_nether-kills` JOIN `lb-players` ON killer=playerid group by playername ) ) as t")); $nejKdr = $kdr[0]; $nejKdrPocet = $kdr[1]; $zbran = mysql_fetch_row(mysql_query("SELECT weapon, nej FROM ( ( SELECT weapon,count(id) AS nej FROM `lb-minecraftie-kills` group by weapon ) UNION ( SELECT weapon,count(id) AS nej FROM `lb-minecraftie_nether-kills` group by weapon ) ) as t order by nej DESC")); $nejZbran = $zbran[0]; $nejZbranPocet = $zbran[1]; ?> |
||
Kajman Profil |
#2 · Zasláno: 11. 4. 2012, 18:12:51
Změřte si, které dotazy jsou pomalé. U nich pak sem napište jejich explain a indexy, které na použitých tabulkách máte.
Případně si statistiky cacheujte, pokud je stačí zaktualizovat jen občas. |
||
Tori Profil |
#3 · Zasláno: 11. 4. 2012, 19:12:24
Trska:
K dotazu na ř.7 můžete doplnit LIMIT 1 . Vypadá to, že používáte jen první řádek dat, tak je zbytečné tahat z DB celou tabulku.
|
||
Časová prodleva: 12 let
|
0