Autor Zpráva
Trska
Profil *
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
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
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.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: