Query 'crunch'

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
HiddenS3crets
Forum Contributor
Posts: 119
Joined: Fri Apr 22, 2005 12:23 pm
Location: USA

Query 'crunch'

Post 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";
?>
Last edited by HiddenS3crets on Tue Oct 25, 2005 7:35 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I think firstly you should post your queries, perhaps some can be optimized.
Post your table structure too
RobertPaul
Forum Contributor
Posts: 122
Joined: Sun Sep 18, 2005 8:54 pm
Location: OCNY

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

every one of the queries could be a COUNT() query other than the insert...
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Doing a mysql_free_result() after each one will help a lot too.
Post Reply