My Code works, now I need to speed it up

l008com

    <?
	//Connect to Database
	$link = mysql_connect("localhost","******","*********") or
		die ("Could not connect to database");

mysql_select_db("pickasite") or
	die ("Could not select database");

//Update A Counter of how many times we've done this
$randomhits = mysql_query("SELECT `randomhits` FROM `stats`",$link);
$row = mysql_fetch_row($randomhits);
$randomhits = ((integer)$row[0])+1;
mysql_query("update `stats` set randomhits=$randomhits",$link);


//Seed a random number machine
mt_srand((double) microtime()*1000000);

//Number of total items in this table
$bignumber =  mysql_query("select count(*) from urldatabase", $link);
$myrow = mysql_fetch_row($bignumber);
$myRandom = mt_rand(1,$myrow[0]);

//Select an item out of urldatabase at random, between the begining and end of the table.
$myURL = mysql_query("select url from urldatabase limit $myRandom, 1", $link);
$myresult = mysql_fetch_row($myURL);
$actualaddres = stripslashes($myresult[0]);

//The End
mysql_close($link);
?>

Here's the main chunk of my code. It basically pulls an item from a database at random. The problem is, the table 'urldatabase' as a little over 4 million items in it, and is about 165 MB in size. It usually takes about 5 second or more to read the item out. So I'm wondering if there are any ways I can speed this up somehow, optimize it a bit? (besides faster hardware :-P )

drawmack

Zippity do dah, Zippity ay

<? 
    //Connect to Database 
    $link = mysql_connect("localhost","******","*********") or 
        die ("Could not connect to database"); 

mysql_select_db("pickasite") or 
    die ("Could not select database"); 

//Update A Counter of how many times we've done this 
//Speed up here by going from two queries with external 
//math to one query with internal math.
      $sql = "UPDATE stats SET randomhits = (randomhits + 1)";
      mysql_query($sql,$link); 


//Seed a random number machine 
mt_srand((double) microtime()*1000000); 


//Speed up here by using order by instead of a bunch of //extraneous coding
    $sql = "SELECT url FROM urldatabase ORDER BY rand()";
    $myURL = mysql_query($sql, $link); 
    $myresult = mysql_fetch_row($myURL); 
    $actualaddres = stripslashes($myresult[0]); 

//The End 
mysql_close($link); 
?>

l008com

If I read your code correctly, does it essentially create a 'result table' containing all the items of urldatabase only in a random order, then just chose the first one? I modified my code and now it doesn't seem to work, and that would explain why, if its trying to grab all 4 million items out of the database instead of just one random one. Or am I way off?

l008com

Yeah I tried your code and my server started shitting bricks, no lie!! That technique doesn't seem to work on really large databases. But your leaner counter code works better!! Any other ideas for the random picker?

drawmack

throw limit 0,1 on the end so it only grabs the first record of the set instead of grabbing all of them.

promania

if you really want to speed up divide your table to 5(maybe less or more)
table names like t1 t2 t3 t4 t5

then randomize 5 then

you found 3

then use sql above

SELECT * FROM t3 ORDER BY RAND() LIMIT 1

when u count 4 million then select the 2 millionth your database goes over 6 million records. by this it will be less

l008com

Ha ha your no perl thing is funny!!! I just noticed it... I'll try that and get back to you.

l008com

yeah adding the LIMIT feature doesn't really seem to help at all. The problem finding a good way to devide up the data into multiple databases. Although since I don't really need any order at all, I could just make a loop and go around in order adding them one by one. I'll have to give that a shot. I'm only running a 350MHz G3 server though, and ideas how many databases would be a good amount to use? I'm thinking a nice round number like 10. that will give me 400,000 each, about.

drawmack

not diferent databases, different tables in the same database.

you're insert routine need not be fast since it is only rarely executed, keep a text file telling you what table to insert into next. Then use a variable to select the table to pull from.

tekky

Originally posted by drawmack
not diferent databases, different tables in the same database.

you're insert routine need not be fast since it is only rarely executed, keep a text file telling you what table to insert into next. Then use a variable to select the table to pull from.

or have the insert function do a COUNT() on each table and then follow these rules:

1) write to smallest table # first
2) if any tables are equal in size write to smallest table # first

COUNT() shouldnt slow you down much even on large tables I don't believe.... then then you have no file to worry about keeping track of either.

l008com

My table is 'built' all at once over a 24 hour or so period, then the table stays pretty much untouched until the next one is built from scratch to replace it, so i could have it just spin around using a simple int variable. It should be easy.

tekky

yah something that just increments $var++
if $var > 5 then $i = 1 or something like that would work for that 😉

ahundiak

Going back to your original post, you are trying to pick one record at random from the large url table. Correct?

Build the table with a unique index incremental integer key (call it say url_id), perhaps using auto increment. Now:
SELECT url FROM urldatabase WHERE url_id = $myRandom;

The will work very fast pretty much regardless of the number of records you have. It does not sound like you delete individual records so you don't have to worry about missing ids.

l008com

Originally posted by ahundiak
Going back to your original post, you are trying to pick one record at random from the large url table. Correct?

Build the table with a unique index incremental integer key (call it say url_id), perhaps using auto increment. Now:
SELECT url FROM urldatabase WHERE url_id = $myRandom;

The will work very fast pretty much regardless of the number of records you have. It does not sound like you delete individual records so you don't have to worry about missing ids.

That will be faster than
SELECT url FROM urldatabase LIMIT n,1
?

l008com

Actually, on second thought... I don't delete items yet, but as soon as i get the basic stuff working, I need to put a filter on the loader program that makes the DB so after its full it goes through and removes items that match my blacklist table. So I don't think that will work.

tekky

Originally posted by l008com
Actually, on second thought... I don't delete items yet, but as soon as i get the basic stuff working, I need to put a filter on the loader program that makes the DB so after its full it goes through and removes items that match my blacklist table. So I don't think that will work.

Have your builder scan the blacklist BEFORE adding them.... then no worries 😉

ahundiak

Selecting a single record using an indexed key will much(much much much) faster than using limit. Databases are dessigned to make looking up with keys to be very fast.
As long as the number of deleted records is fairly small (compared to the total number of records) then you can simply try a query and if it fails (because the record is deleted) then try another one. Even doing several queries will be very fast compared to your original method.

If you already have a blocklist table then you could lookup a record before adding to your master list and toss it out?

Or, after building the url list, run through it and build a second index table with only the non-blacklisted records in them.

Or, build a comma delimited list of say 10 random id's and use WHERE IN to query them. In all likely hood, at least one record would be returned.

I guess the first thing you might do is to go through a sql tutorial or two and learn a bit about indexes. Whatever you do, don't break large tables into small tables for performance reasons. It's just not the right road to go down when indexes will work.

l008com

Would this work..
Make the database with the url and make the ID not unique, set them all to the same #1. Then go through and delete the ones I don't need. Then go through the table in order and renumber all the IDs starting from 1. ?

tekky

Originally posted by l008com
Would this work..
Make the database with the url and make the ID not unique, set them all to the same #1. Then go through and delete the ones I don't need. Then go through the table in order and renumber all the IDs starting from 1. ?

work... sure... efficient... no.... just have your build script throw out rather than ADD blacklisted items from the get go

ahundiak

Sure. It's a bit heavy on processing but I gather you only do this infrequently. And it will certainly make sure there are no holes.

You will probably want to create the actual index as the last step in the process i.e.
CREATE TABLE url ( url_id INTEGER, url CHAR(40));
Load up the initial list of url's with url_id = 0.
Delete black listed url's.
Set the url_id values.
CREATE UNIQUE INDEX url_id_index ON url(url_id);
Ready to rock.

By the way, you might still want to read up on indexes. Indexes are very useful especially in large databases. I gather you have not indexed your url names? How do you prevent dups?