Speeding up code
Posted: Sat Jun 10, 2006 11:05 am
I have a database of models and I made a script to randomly select a model every 30 minutes and show her info. I have 1 table of models. Each model has their own row. I have 2 more tables to keep up with the currently selected model, and the previous one. This page loads fast once the models are selected, but every 30 minutes the script takes .5 - several seconds to load. Is there something obvious I'm not seeing that can speed things up?
How the "random" works: so I didn't have to query all the rows of models, shuffle their id's, then re-update, I decided to just use them in order. I add a random number (1-5) to the previous model to save time.
Whats taking so long for the script to run? The rand()s, the sql queries, or something else?
Thanks much!
How the "random" works: so I didn't have to query all the rows of models, shuffle their id's, then re-update, I decided to just use them in order. I add a random number (1-5) to the previous model to save time.
Whats taking so long for the script to run? The rand()s, the sql queries, or something else?
Code: Select all
<?
$dsngs = 4; // number of templates
$db = ""; // mySQL database name
mysql_connect("localhost","","");
mysql_select_db("$db");
$result = mysql_query("select * from current");
$row = mysql_fetch_row($result);
$desva = $row[3]++;
$modva = $row[2];
$datg = date(g);
if (date(i) < 30){
$min = 1;
} else {
$min = 2;
}
// if its been 30 minutes, get new model
if ($row[0] != $datg OR $row[1] != $min){
// delete previous table
$sqldr = "DROP TABLE `prev`";
mysql_query($sqldr);
// rename current to previous
$sqlre = "ALTER TABLE `current` RENAME `prev` ;";
mysql_query($sqlre);
// recreate current
$sqlcr = 'CREATE TABLE `current` ('
. ' `timeh` INT(15) NOT NULL, '
. ' `timem` INT(15) NOT NULL, '
. ' `model` INT(15) NOT NULL, '
. ' `design` INT(15) NOT NULL, '
. ' `set` INT(15) NOT NULL, '
. ' `stats` INT(15) NOT NULL'
. ' )'
. ' TYPE = myisam';
mysql_query($sqlcr);
// get design
if ($desva > $dsngs) {
$designf = 1;
} else {
$designf = $desva;
}
// get model
$uult = mysql_query("select * from `models` order by 'order' desc limit 0,1");
$prevu = mysql_fetch_row($uult);
$mod1 = rand(1, 5);
$mod2 = $modva + $mod1;
if ($mod2 > $prevu[0]) {
$mod2 = rand(1, 3);
}
$clt = mysql_query("select * from `models` WHERE mid = $mod2");
$curr = mysql_fetch_row($clt);
// get hosted
$curh = rand(1, $curr[13]);
// get set
$curs = rand(1, $curr[14]);
// insert model into current table
$sqlinc = "INSERT INTO `current` ( `timeh` , `timem` , `model` , `design` , `set` , `stats` ) VALUES ('" . $datg . "', '" . $min . "', '" . $curr[1] . "', '" . $designf . "', '" . $curs . "', '" . $curh . "');";
mysql_query($sqlinc);
$result = mysql_query("select * from current");
$row = mysql_fetch_row($result);
}
// models are ready, begin page
$pesult = mysql_query("select * from prev");
$prrow = mysql_fetch_row($pesult);
$nowsql = mysql_query("select * from `models` WHERE mid = $row[2]");
$now = mysql_fetch_row($nowsql);
$beforesql = mysql_query("select * from `models` WHERE mid = $prrow[2]");
$before = mysql_fetch_row($beforesql);
// where the template and model info will go
echo "<p>Current model: " . $now[4] . "<p>";
echo "<p>Previous model: " . $before[4] . "<p>";
?>