Page 1 of 1

Query 'crunch'

Posted: Tue Oct 25, 2005 7:32 pm
by HiddenS3crets
I have almost 10 queries running one after another and it is making my page load slowly, so i'm wondering if there is a way to make them process faster or some other method to make the page load quicker?

Code: Select all

<?php
$query = "SELECT * FROM `members`";
$result = mysql_query($query) or die(mysql_error());
$rows = mysql_num_rows($result);
{
echo "<b>Total Members:</b> $rows<br>";
}
$query = "SELECT * FROM `tutorials` WHERE `echo` = 'y'";
$result = mysql_query($query) or die(mysql_error());
$rows = mysql_num_rows($result);
{
echo "<b>Total Tutorials:</b> $rows<br>";
}
$query = "SELECT * FROM `templates`";
$result = mysql_query($query) or die(mysql_error());
$rows = mysql_num_rows($result);
{
echo "<b>Total Templates:</b> $rows";
}
?>
<br><b>Total hits:</b> <?php include('http://hiddenskills.net/counter.php');
$ip = $_SERVER['REMOTE_ADDR'];
$date = date('M d, Y');
$month = date('M');

$query = "INSERT INTO `hits` VALUES('$date','$ip')";
$result = mysql_query($query) or die(mysql_error());

$query = "SELECT DISTINCT(`ip`) FROM `hits` WHERE `date` LIKE '%$month%'";
$result = mysql_query($query) or die(mysql_error());
$unique_hits = mysql_num_rows($result);

$query = "SELECT DISTINCT(`ip`) FROM `hits` WHERE `date` = '$date'";
$result = mysql_query($query) or die(mysql_error());
$unique_today = mysql_num_rows($result);

$query = "SELECT * FROM `hits` WHERE `date` LIKE '%$month%'";
$result = mysql_query($query) or die(mysql_error());
$total_month = mysql_num_rows($result);

$query = "SELECT * FROM `hits` WHERE `date` = '$date'";
$result = mysql_query($query) or die(mysql_error());
$total_today = mysql_num_rows($result);

echo "<br><b>Total ($month):</b> ".number_format($total_month)."<br><b>Unique Hits ($month):</b> ".number_format($unique_hits)."<br><b>Unique Hits Today:</b> ".number_format($unique_today)."<br><b>Total Today:</b> ".number_format($total_today)."<br>";

$time = time() - 300;
$query = "SELECT * FROM `members` WHERE `online` > '$time'";
$result = mysql_query($query) or die(mysql_error());
$users = mysql_num_rows($result);

$query = "SELECT * FROM `online_guests` WHERE `time` > '$time'";
$result = mysql_query($query) or die(mysql_error());
$guests = mysql_num_rows($result);

$total = ($users + $guests);

echo "<b>Users online:</b> $total";

mysql_select_db('hiddenskills_net_-_phpbb') or die(mysql_error());

$query = "SELECT * FROM `phpbb_users`";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_num_rows($result);

$query = "SELECT * FROM `phpbb_posts`";
$result = mysql_query($query) or die(mysql_error());
$posts = mysql_num_rows($result);

echo "<br><b>Forum Members:</b> $total<br><b>Forum Posts:</b> $posts";
?>

Posted: Tue Oct 25, 2005 7:34 pm
by John Cartwright
I think firstly you should post your queries, perhaps some can be optimized.
Post your table structure too

Posted: Tue Oct 25, 2005 8:56 pm
by RobertPaul
For your first three queries query, just SELECT count(*) FROM `members` (or `tutorials` or `templates` as the case may be).

Actually ... in (almost?) all your queries you can use count(*) in some form or another. There's no reason to have it SELECT * all that data when all you need is a row count.

Posted: Tue Oct 25, 2005 9:03 pm
by feyd
every one of the queries could be a COUNT() query other than the insert...

Posted: Wed Oct 26, 2005 4:31 am
by onion2k
Doing a mysql_free_result() after each one will help a lot too.