Speeding up the process

[john010117]

Hello.

I have simple news management system on my index.php page. The following is the code for the main part of the code.

 

<?php
  
include 'info.php'; ?>
  
  // Connect to server
  $dbac = mysql_connect($db_host,$db_user,$db_pass); 
  $today = getdate();
  
  // Select database
  mysql_select_db ($db_name) or die ("Cannot connect to database");
  
  // Get date from _GET param or current date if not available
  $_GET['date'] = @trim(stripslashes($_GET['date']));
  $date = ($_GET['date'] && !empty($_GET['date'])) ? date('Y-m-d', strtotime(trim($_GET['date']))) : date('Y-m-d');
  
  $result = mysql_query('SELECT * FROM news WHERE Date = "' . $date . '" ORDER BY Time');
  $curtime = time();
  if ($result && mysql_num_rows($result)) {
    $numrows = mysql_num_rows($result);
    $rowcount = 1;
         
while ($row = mysql_fetch_assoc($result)) { 
  print "<b>{$row['Title']}</b><br />
{$row['News']}<br />
<b>Posted by:</b> {$row['Posted_by']}   ({$row['Date']}   {$row['Time']})<br /><br />"; 
}      
      print "<br />";
      ++$rowcount;
    }
  
?> 

 

I have other pages that have coding related to this, but I believe that this is the main part. It works fine, but it takes a while for the page to load (compared to the loading time without the code, which takes less than a second). Is there any way to speed it up?

[btherl]

Is your news table indexed by date?

[john010117]

Yes, I believe so.

[btherl]

Can you verify that there is an index on it?  No index (or something preventing the index being used) is the only reason I can imagine that that query would run slowly.

 

If it's not the query running slowly, it may be the database connection taking time.  Is the database running on the same machine as the code?  One possibility there is that name resolution for the database hostname may be slow.  That won't be a problem if the hostname is 'localhost' though   You might want to time mysql_connect() and mysql_query() to find exactly where the time is being taken.

[john010117]

I have confirmed that the index is on the field "date". Also, I do not believe my code has something that will block the index from being used. My hostname is indeed "localhost". How do you time it?

[btherl]

There's a standard function I use:

 

function getmicrotime() {
        list($usec, $sec) = explode(" ", microtime());
        return ((float)$usec + (float)$sec);
}

 

Call this before and after each part of code which you think may take some time, then print out the result like this:

 

$start_time = getmicrotime();
mysql_connect(...);
$end_time = getmicrotime();
print 'mysql_connect() took ' . sprintf('%.2f', $end_time - $start_time) . ' seconds<br>';