[RESOLVED] Help speeding up script

mrbaseball34

I'm trying to speed up a script that we use to let bots crawl our site. This is the only page that the bots are allowed to access.

We have over 2 million records in our database and we want one column from the table linv_inventory shown in an HTML table paged in 10,000 row increments.

  // counting the offset 
  // $rowsPerPage = 10000
  $offset = ($pageNum - 1) * $rowsPerPage;
  $query = "SELECT `inventory_part_number` FROM `linv_inventory` ORDER BY `inventory_part_number` LIMIT $offset, $rowsPerPage";

  $result = mysql_query($query) or die('Error, query failed');

Which of these would be faster? The timing results that I have gotten have really
been inconclusive.

In the first, we query the first 10,000 records, extract them into an array, then iterate through the array in a foreach.

  $element = array(true);
  while($row = mysql_fetch_array($result))
    $element[] = trim($row[0]);

  echo "<table width=\"100%\" cellspacing=\"2\" cellpadding=\"3\">\n";
  $i = 0;
  foreach($element as $cell) {
    echo '<td>' . $cell . '</td>';
    if($i%5==0) {
      echo "  </tr>\n";
      echo "  <tr>\n";
    }
    $i++;
  } // foreach($element as $cell)
  echo "  </tr>\n";
  echo "</table>\n";

In the second, we just iterate over the result set using mysql_fetch_array().

  while($row = mysql_fetch_array($result)) {
    echo "  <tr>\n";
    echo '<td>' . $row['inventory_part_number'] . '</td>';$row = mysql_fetch_array($result);
    echo '<td>' . $row['inventory_part_number'] . '</td>';$row = mysql_fetch_array($result);
    echo '<td>' . $row['inventory_part_number'] . '</td>';$row = mysql_fetch_array($result);
    echo '<td>' . $row['inventory_part_number'] . '</td>';$row = mysql_fetch_array($result);
    echo '<td>' . $row['inventory_part_number'] . '</td>';$row = mysql_fetch_array($result);
    echo '<td>' . $row['inventory_part_number'] . '</td>';
    echo "  </tr>\n";
  } // while($row = mysql_fetch_array($result))

Are here any other means that I may use to make this faster as this script has been causing problems due to the number of bots that hit it simultaniously and brigning my database down to a crawl.

johanafm

Do the work once and serve the same stuff every time. I.e. go through your 2M rows and create 200 static html files that you serve. Recreate file(s) on change.

mrbaseball34

This data changes many times. It is not static.
Users may upload inventory and it automatically be imported at any time.

Desdinova

why don't you benchmark it? just time the script. $start = time(); at the very first line, $end = time(); at the end of the script. echo the difference. run the script a few times to see how long it takes. once you know, try the other version and time that.

I think the script itself has some room for improvement to speed things up too. $i%5==0 can easily be rewritten to ($i==4) and then setting $i = 0; when true. also, why first put it in elements[] and not doing what the foreach does at that time? this is a double iteration over the same data.

also, note that if you use a LIMIT with a start and end, the db actually COUNTS all the records up to start, then puts them in mem until reaching the end, and returns that info. If possible, consider doing a WHERE id BETWEEN start, end. This will be faster, especially when start gets higher.

Additionally, foreach() creates a copy of the var. You might want to do a for-loop as this could be more efficient in terms of mem usage. Also, as you're doing all those echoes, the output is buffered. Depending on your settings I'd say this could take up quite some memory. See if you can force buffer output to decrease mem usage.

Also think about how much of an issue it is if you're data is behind 5 or 10 minutes. You could use a cron to create static files that you recreate automatically every X minutes/seconds/whateff. The main advantage is that you only have ONE cronjob (every x minutes) to create the datafiles (database load), but serve the cached files to the MANY bots. Or as johanafm suggest, update the cached files on change.