Is this unefficient?

[Michdd]

Would using this to generate a rank from a player from the database, if there were to be thousands of players in the DB, and this script would have to be run every couple seconds, would this be bad?

 

//Get the name from the URL
$username = $_REQUEST['name'];

//Get data from the database
$result = mysql_query("SELECT * FROM `table` ORDER by exp desc") 
or die(mysql_error());  
$rank = 1;
while($row = mysql_fetch_array( $result )) {
$check = $row['name'];
//When the user is found output the rank in imagestring() then break the loop.
if($check == $username){
imagestring($im, 5, 0, 0, $rank, $textcolor);
}
//If the loop was not broken, increment the rank by 1
$rank++;
}

 

If so would there be any other way? Currently the only other thing that was thought of was to only generate the ranks every 2 hours or so and store it in a row in the db.

[Yesideez]

Are the ranks shown on every page?

 

Why not have something inside an include and call that only on the pages that need it?

[Michdd]

Are the ranks shown on every page?

 

Why not have something inside an include and call that only on the pages that need it?

The ranks are actually generated on a php GD image. And those are used on the Game's Forums, and many people use the player cards, so they're generated very, very frequently.

[Yesideez]

At the end of the day the best solution is to use as little amount of code as is possible.

 

You're looping the table "table" - if you've got a lot of rows in there that's soon going to put a strain on the server...

[premiso]

At the end of the day the best solution is to use as little amount of code as is possible.

 

You're looping the table "table" - if you've got a lot of rows in there that's soon going to put a strain on the server...

 

Thats the point of pagination....actually. Which would be more code, but only run x amount per page.

 

Little code as possible is not always a good thing, depending on the circumstances. In this scenario, he would want more code to break up the results and display links to pages to display the other data.

[Michdd]

At the end of the day the best solution is to use as little amount of code as is possible.

 

You're looping the table "table" - if you've got a lot of rows in there that's soon going to put a strain on the server...

Well, how would I be able to generate the ranks in real time without straining my server?

[mrdamien]

Generating them real-time everytime would probably strain your server anyways.

 

Instead, set up a cron job to generate the image every X amount of minutes, and store it. Then just link to that image.

[Yesideez]

Just had a quick look one thing is the use of checking the username in "table" - if you can check using the user's ID number that would be a LOT quicker.

 

When paginating data the entire table still has to be read and sorted before the results can be returned.

[premiso]

Just had a quick look one thing is the use of checking the username in "table" - if you can check using the user's ID number that would be a LOT quicker.

 

When paginating data the entire table still has to be read and sorted before the results can be returned.

 

Yea, but MySQL is built to be amazingly fast and can do this very quick even with hundreds of thousands, even millions of records. The DB won't slow him down one bit, its the generating the image for the whole table/displaying the whole table that will make the script crawl.

 

With the return rows being limited to pagination the script will run smooth. Take this site for example, there are 25 topics per page with 2356 pages, and yet it displays dang quick.

 

That is a total of 58,900 rows, just for this forum, now tally all the forums up together....

 

 

[Yesideez]

Yeah, I see your point...

[genericnumber1]

Why not just store the ranks with the users, and weekly, daily, whateverly calculate the user's ranks? The vast majority of users (in a large enough system) wont be ranked high enough for them to really care about their exact rank at any exact moment.

[cooldude832]

You can do this in MySQL

 

You just need to figure out the row number of that requested username which you can do via MySQL

 

You can do something like

 

Find the number of rows that are >= the users EXP

Find the total number of rows

 

2 queries with 2 data points makes a lot more than 1 query with X rows = to the number of users you have

 

 

There may be an alternative MySQL solution but that is the best off the top of my head

[corbin]

cooldude832, that's a good solution, but what about indexes?

 

If you index the exp field, everytime the table changes the index will have to be rebuilt, but without an index, a table-wide COUNT could be disastrous.  I guess the table could be replicated or something.  Or, maybe rebuilding the index or doing it without an index wouldn't be too much of a problem.

 

 

Bleh.....  Now this is really bugging me lol.

 

 

Edit:  Just found http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html googling, and finding it quite interesting so far.

[cooldude832]

cooldude832, that's a good solution, but what about indexes?

 

If you index the exp field, everytime the table changes the index will have to be rebuilt, but without an index, a table-wide COUNT could be disastrous.  I guess the table could be replicated or something.  Or, maybe rebuilding the index or doing it without an index wouldn't be too much of a problem.

 

 

Bleh.....  Now this is really bugging me lol.

 

 

Edit:  Just found http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html googling, and finding it quite interesting so far.

 

 

You can always take into account a secondary table to only manage ranks that stores EXP  and UserIDs only so you are working on a smaller table but I see your point

 

Its trial and error when you get into efficiency

[corbin]

I think this is one of those situations where performance is going to be bad.  It's just a matter of how bad performance is going to be lol.

[.josh]

//Get the name from the URL
$username = mysql_real_escape_string($_REQUEST['name']);

//Get data from the database
$sql = "SELECT v1.name, COUNT(v2.exp) AS rank
          FROM table v1
          JOIN table v2 ON v1.exp < v2.exp
          OR (v1.exp = v2.exp AND v1.name = v2.name)
          GROUP BY v1.name
          HAVING v1.name = '$username'
          ORDER BY rank";

$result = mysql_query($sql) or die(mysql_error()); 
if ($row = mysql_fetch_assoc($result)) {
   imagestring($im, 5, 0, 0, $row['rank'], $textcolor);
} else {
  // username was not found, do something
} // end if..else $row

[genericnumber1]

showoff ...I really should learn advanced SQL.

[blueman378]

lol i agree...

[corbin]

showoff ...I really should learn advanced SQL.

 

 

That's just a complex join with a HAVING clause >.>.

[cooldude832]

showoff ...I really should learn advanced SQL.

 

 

That's just a complex join with a HAVING clause >.>.

 

 

agreed.

 

There is probably some innoDB way to do this efficently or even a procedural code to assign rank everytime an adjustment is made

 

[.josh]

some sql dbs offer a rank() function(or construct or whatever) that more or less does exactly that.  mysql is not one of those dbs.

[cooldude832]

I gotta image that MySQL has addressed figuring the rows # in a specific query based on the total number of results because it just seems to practical not to have functionalized.

[corbin]

I gotta image that MySQL has addressed figuring the rows # in a specific query based on the total number of results because it just seems to practical not to have functionalized.

 

 

Just because something is a function doesn't mean it's efficient.

 

 

I wonder how other RDBs do it....