php - 我如何优化这些查询？

Question

我知道有 2 个领域是流程问题，但我不知道如何改进它们。

此处显示有问题的 2 个地方

http://i.imgur.com/Kb5Bv.png

以及相应的查询

1区

$nresult = mysql_query("SELECT time
                       FROM log
                       ORDER BY time
                       LIMIT 1") or die(mysql_error);

$nr = mysql_fetch_assoc($nresult);

$aresult = mysql_query("SELECT *
                       FROM log") or die(mysql_error);

$an = mysql_num_rows($aresult);

$aresult = mysql_query("SELECT AVG(players)
                       FROM log") or die(mysql_error);

$ap = mysql_fetch_assoc($aresult);

$average = round($an/((time()-$nr['time'])/60/60), 2);

echo "<p class=\"tf2\"><span>{$an}</span> items since " . date("F j, Y", $nr['time']) . " at " . date("g:i a", $nr['time']) . ".<br /><span>" . $average . "</span> items received per hour (<span>" . round($average / $ap['AVG(players)'], 2) . "</span>/player/hour).<br />Showing <span>{$_SESSION['limit']}</span> items per page.</p>";

2区

if (!empty($_GET['s']) && !empty($_GET['t']) && !empty($_GET['m'])&& !empty($_GET['q'])) {

    if ($_GET['m'] != 'all') {

        $m = "AND method = {$_GET['m']}";
    }
    else {

        $m = '';
    }

    if ($_GET['q'] != 'all') {

        $q = "AND quality_id = {$_GET['q']}";
    }
    else {

        $q = '';
    }

    if ($_GET['s'] == ' ') {

        $s = '1=1';
    }
    else {

        $s = "{$_GET['t']} LIKE '%" . mysql_real_escape_string($_GET['s']) . "%'";
    }

    $num_result = mysql_query("SELECT *
                              FROM log
                              LEFT JOIN item_definition
                              on log.item = item_definition.item_definition_desc
                              LEFT JOIN method
                              on log.method = method.method_id
                              LEFT JOIN item
                              on item_definition.item_definition_id = item.item_id
                              LEFT JOIN server
                              on log.server = server.ip
                              LEFT JOIN quality
                              on log.quality = quality.quality_id
                              WHERE {$s}
                              {$m}
                              {$q}")
                              or die(mysql_error());
}
else {

    $num_result = mysql_query("SELECT *
                              FROM log") or die (mysql_error());
}

$total = mysql_num_rows($num_result);

echo "<center>" . $total . " results</center><br />";

直播页面在这里http://www.tf2items.711clan.net/

如您所见，它需要很长时间才能加载。

如有任何提示，我们将不胜感激。

Answer 1

我没有看到任何 LIMIT/OFFSET 命令。如果您在商店中使用分页，也许这会给您带来提升。或者您也可以尝试将其缓存在某个地方(如果搜索过滤器没有改变)。