Optimizing Database Update

Ryukotsusei

Hello all. I am still very new when it comes to PHP and MYSQL, and I have a bit of a problem. I have a website that is connected to another site through its API, which I have permission to access. This site is for virtual pets that hatch and grow up by people viewing and clicking them. My site has a database with three tables. One is for "ER" pets that have 4 days or less to mature before dying. The second is for eggs (the first stage of growth), and the third is for hatchlings (the second stage before being fully grown).

I currently have a cron job which executes a PHP script every 10 minutes to keep these databases clean. It works, but it takes an extraordinarily long amount of time to do so (when I tested the script directly, it took 10 minutes or more to complete). I really want to optimize this script to streamline things and make it more efficient.

<?php

 $username="*******";
 $password="*******";
 $database="*******";
 $host="********";
 mysql_connect("$host", "$username", "$password") or die(mysql_error());
 mysql_select_db("$database") or die(mysql_error());

 function update($type)
 {
   $query="SELECT * FROM $type";
   $result=mysql_query($query);
   $num=mysql_numrows($result);

   $sql = mysql_query("SELECT Code FROM $type LIMIT $num");
   while($object = mysql_fetch_object($sql))
   {
   $codevar = $object->Code;

   $data = unserialize(file_get_contents("http://dragcave.net/**********/view/$codevar"));

       $data = array_pop($data['dragons']);
       $code=$data['id'];
       $egg=$data['hatch'];
       $hidden=$data['start'];
       $views=$data['views'];
       $unique=$data['unique'];
       $clicks=$data['clicks'];
       $hours=$data['hoursleft'];

             $querynull = "DELETE FROM NICU WHERE Code=''";
             mysql_query($querynull);

             if($hidden == "0") //if hidden
             {
               $query = "DELETE FROM NICU WHERE Code='$code'";
               $query1 = "DELETE FROM Eggs WHERE Code='$code'";
               $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
               mysql_query($query);
               mysql_query($query1);
               mysql_query($query2);
             }
	elseif($hours == -2) //if dead
            {
              $query = "DELETE FROM NICU WHERE Code='$code'";
               $query1 = "DELETE FROM Eggs WHERE Code='$code'";
               $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
               mysql_query($query);
               mysql_query($query1);
               mysql_query($query2);
            }
	elseif($hours == -1) //if adult or frozen
            {
              $query = "DELETE FROM NICU WHERE Code='$code'";
               $query1 = "DELETE FROM Eggs WHERE Code='$code'";
               $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
               mysql_query($query);
               mysql_query($query1);
               mysql_query($query2);
            }

              if($egg == "0" && $hours > 96) //if egg
              {
              $query = "INSERT INTO Eggs VALUES ('','$code')";
              mysql_query($query);
              $status="$code is in the hatchery.";
              }
	elseif($egg != "0" && $hours > 96) //if hatchling
            {
              $query1 = "DELETE FROM Eggs WHERE Code='$code'";
              mysql_query($query1);
              $query = "INSERT INTO Hatchlings VALUES ('','$code')";
              mysql_query($query);
              $status="$code is in the nursery.";
              }

              if($hours > 96) //if not ER
              {
                $query = "DELETE FROM NICU WHERE Code='$code'";
                mysql_query($query);
              }
	elseif($hours <= 96 && $hours != -2 && $hours != -1) //if ER
            {
              $query = "INSERT INTO NICU VALUES ('','$code')";
              mysql_query($query);
              $status="$code is in the ER.";
              }
  }
     }

 update('NICU');
 update('Eggs');
 update('Hatchlings');
  ?>

This is what the script looks like right now.

Whenever it runs, I want it to comb through the databases and make sure everything is in the correct table. I want anything with 4 days or less to be in NICU. Anything that has hatched should be moved to the Hatchlings table, and anything that has grown, died, or is hidden should be removed from the site. All of this information is taken from the other site's API.

There is probably a much better way to go about it than what I have, but with my limited knowledge it was the best I could come up with. So what I'm asking is just some advice on how I can streamline this script. Or maybe a way I can completely rethink my process to make it as easy and efficient as possible.

Thank you for your help. 🙂

ixalmida

Have you tried adding some timers throughout your code? You need to narrow down what parts of your code take the longest. I'd be willing to bet that...

$data = unserialize(file_get_contents("http://dragcave.net/**********/view/$codevar"));

...takes the longest since you're accessing a remote db. I doubt there's much you can do about it though.

bradgrafelman

Some other comments...

Why do you do a SELECT * query to get the number of records in the table, and then use that in a LIMIT clause? If you're just trying to loop through every row, then don't use a LIMIT clause at all...

And besides, if you did want to get the total number of rows, doing a 'SELECT COUNT()' is usually more optimized for MyISAM tables (not sure about InnoD😎.
Why are you running this query:
```
                 $querynull = "DELETE FROM NICU WHERE Code=''"; 
                 mysql_query($querynull);
```
on every iteration of the loop?! I don't see why you would want to run that query any more than once (other than to slow down the script, of course).
Instead of these three separate queries:
```
                  $query = "DELETE FROM NICU WHERE Code='$code'"; 
                   $query1 = "DELETE FROM Eggs WHERE Code='$code'"; 
                   $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
```
have you instead considered using InnoDB tables with foreign key relations? Doing so would allow you to set the "ON DELETE CASCADE" option in all of the child tables (assuming you have one main table that contains all Code values), at which point you'd need only to DELETE FROM the main table and the SQL server would take care of the rest.

If not, you can still combine all three of those queries into a single query - just use a comma-separated list of table names.
Speaking of the above queries, there's no need to copy and paste the same code over and over and over again just to have it apply to different conditions. Instead, combine all of your if() statements into one (e.g. using OR logic).
Also, rather than running a DELETE query after each row, you could instead store all of the Code values that need to be deleted into a variable (an array would work nicely) and then run a single DELETE statement at the end (e.g. using MySQL's IN clause to list all of the values). Depending upon the number of codes that usually get deleted, this could be far more efficient.
Is there a reason you chose this DB structure? Why use separate tables for everything and have the need to constantly INSERT and DELETE things? Why not just have all of the data in one table and use a separate column (or a separate table) to store which location the data belongs to?

It would be a lot more efficient to simply UPDATE a single column when the location changes rather than doing a DELETE and then an INSERT query.

Ryukotsusei

Wow you give me a lot of things to think about. I have already determined the slowdown is most likely caused by the file_get_contents, and I know there's little I can do about that. But I will definitely think about reorganizing the tables and cleaning up the if statements. I guess I figured it would be more organized this way, but it probably would be more efficient to do it like you said.

Thank you for the advice. 🙂