URGENT - Server has locked my index file for overloading mySQL!

[hedgehog90]

My site is www.gpstudios.com, to view the site go to http://www.gpstudios.com/games.php

This happened a month ago. After many emails with the hosting company it was discovered that someone else on the shared server was throttling everyone elses.

Then the problem was gone.

 

But today it has happened again...

I received an email informing me that they have blocked my index.php. Within the email they included a long list of mysql queries that supposedly overloaded the server:

 

| 194991 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto        | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 195062 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto        | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 195314 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto        | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 195496 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto        | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

| 195602 | hedgehog_gpgame2 | localhost                        | hedgehog_gpgameto        | Query  | 0    | Creating tmp table | Select * from games ORDER BY RAND() LIMIT 1                                                          |

etc....

 

This is a query that is called on every page load.

We have a table of over 500 games.

 

So, I emailed them and they replied back telling me they're pretty certain it's my fault this time.

 

Here's my argument though:

- The site's traffic is 1/3 of what it was 6 months ago. On some days it was 5x more than it is now. Yet we never had a problem. Not a hiccup.

- On everypage load, along with "Select * from games ORDER BY RAND() LIMIT 1", there is another query called of almost exactly the same code: Select * "from games ORDER BY RAND() LIMIT 0,4"

Now correct me if I'm wrong, but isn't this 2nd query more intensive than the first that they seem to have a problem with?

 

Is "Select * from games ORDER BY RAND() LIMIT 1" really so intensive??? I believe the games mysql is something around 1.5-2 MegaBytes of data.

 

Can someone please help?

I'm desperate.

[mikosiko]

just read

http://www.paperplanes.de/2008/4/24/mysql_nonos_order_by_rand.html

[Alex]

For more alternatives to ORDER BY RAND() see this post.

[hedgehog90]

Okay, i've tried using this in phpmyadmin:

 

SELECT * FROM games WHERE gameid >= FLOOR(RAND() * (SELECT MAX( gameid ) FROM games)) ORDER BY gameid ASC LIMIT 1

 

It's just not working correctly... I really don't get it.

 

Below is part of the query:

FLOOR(RAND() * (SELECT MAX( gameid ) FROM games)) ORDER BY gameid

Now I have 626 gameids, so, it should generate a random number between 0 and 626... But it's not, it grabs a gameid below 100

 

What I really don't understand, is that this:

SELECT * FROM games WHERE gameid = FLOOR(RAND() * (SELECT MAX( gameid ) FROM games))

 

Notice that I've replaced the ">=" with a "="

 

Now despite having ids equalling 0-626, i sometimes get 0 results... sometimes, I get 2 or 3 results!?

mySQL must have some quirks that I'm not aware of...

 

Can someone explain why the above is not working correctly? because in theory it should retrieve something everytime, between 0-626.

[hedgehog90]

Will this be much more efficient?

 

$sql = "SELECT * FROM games ORDER BY gameid DESC LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
$max = intval($row['gameid']);
$finish = false;
while(!$finish) {
$sql = "SELECT * FROM games WHERE gameid = ". rand(1, $max) ." LIMIT 1";
$result = mysql_query($sql);
if(mysql_num_rows($result) == 1){
	$finish = true;
}
}			
if($result){
$row_listings = array();
$row_listings = mysql_fetch_array($result, MYSQL_ASSOC);
}

 

It appears to be quite a bit faster. Any way I could improve upon it?

[fenway]

ORDER BY is optimized with LIMIT.

[mikosiko]

ORDER BY is optimized with LIMIT.

 

too broad affirmation IMHO ... no always true.. depend on several circumstances.

this worth to be read :

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

[fenway]

Everything depends on circumstances.  What I meant was that if the query was going to be a good choice based on index availability, then adding a LIMIT will likely be optimized.

 

And the converse is definitely true -- without ORDER by, you're getting back a large number of records, which is much slower anyway, regardless.