Speeding up code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Speeding up code

Post by jabbaonthedais »

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?

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>";
?>
Thanks much!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

dropping tables and renaming tables takes a lot of processing, I believe. Why not use the models table? Have a field that marks whether the model is current, previous or neither in some fashion.
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

feyd wrote:dropping tables and renaming tables takes a lot of processing, I believe. Why not use the models table? Have a field that marks whether the model is current, previous or neither in some fashion.
Hmm... That does make sense. Though I have to have those other variables also. Ok so I could do one of these:

1. Add 6 columns to my models table and one of those would signify whatever model is current.

2. Use the 'current' table and have both the current and previous row in there and delete the row instead of deleting and renaming tables.

Which do you think would be faster?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you require those fields, option 2 is better as it's a better use of the storage space.
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

feyd wrote:If you require those fields, option 2 is better as it's a better use of the storage space.
Thanks feyd! I'll try it out. :)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

feyd wrote:dropping tables and renaming tables takes a lot of processing, I believe. Why not use the models table? Have a field that marks whether the model is current, previous or neither in some fashion.
Pardon me if I'm way off as I haven't read anything but glanced over it...

Dropping tables, depending on the RDBMS is as simple as deleting a file in MySQL so it should be pretty quick...

Unless the RDBMS or file system actually *removes* the file using NSA approved seven-pass techniques...I can't see creating/renaming or removing an entire database, or it's tables as being slow??? :?
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

I went with option 2 and it made a huge difference! Basically I just removed the drop table, create table, and replaced all the fetch rows with fetch arrays. Much faster!
Post Reply