Page 1 of 1

Speeding up code

Posted: Sat Jun 10, 2006 11:05 am
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!

Posted: Sat Jun 10, 2006 11:13 am
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.

Posted: Sat Jun 10, 2006 11:17 am
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?

Posted: Sat Jun 10, 2006 11:28 am
by feyd
If you require those fields, option 2 is better as it's a better use of the storage space.

Posted: Sat Jun 10, 2006 11:30 am
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. :)

Posted: Sat Jun 10, 2006 12:14 pm
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??? :?

Posted: Sat Jun 10, 2006 12:31 pm
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!