Causes of High Server Load

PHP
61

Cool, those numbers doesn’t mean much, now if you can run EXPLAIN SELECT * FROM tbl… :smiley:

And what are those 2 queries for?

0.0027
SELECT * FROM `ibf_sessions` WHERE `member_name`='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_time` DESC

0.0025
SELECT * FROM `ibf_sessions` WHERE `member_name`!='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_time` DESC

Oh and btw, you shouldn’t backtick strings or quote integers in MySQL…

Looking at http://www.arcadexl.com/games/action.html?explain=1
You have a query that takes 0.4secs to execute…

SELECT * FROM `ibf_games_list` LEFT JOIN `ibf_games_champs` ON `ibf_games_champs`.`champ_gid`=`ibf_games_list`.`gid` WHERE `ibf_games_list`.`gcat`='3' ORDER BY `ibf_games_list`.`added` DESC LIMIT 0,25

I’m sure this one can be tweaked and sometimes, 2 small queries do a better job than one with bunch of joins and temporary table :wink:

One more thing, it looks like MySQL is caching the results so you may want to add SQL_NO_CACHE after SELECT for more accurate debugging

62

Backtick strings? I have not backticked any strings, only the names of fields and tables, which I’m sure is 100% correct.

Sorting out EXPLAINs now. Sorting out double query now.

63

as stuffs

64

Those two queries, one is for selecting all guests (no-names) from the table and the other for selecting active members. I can probably shorten that now.

Does EXPLAIN actually execute UPDATEs/DELETEs?

65

EXPLAIN only works with SELECT
As for those 2 queries to ibf_sessions, I would suggest you to either grab them all and process with PHP (what IPB do on the board index) or run a SELECT for those with a member_id and a COUNT(*) for the guests

66

Would a PHP rand() from an array selected from the database be quicker than doing ORDER BY rand()? … from a table with currently 170 rows, likely to end up about 1000 in the future?

Something like this:

$result = mysql_query( “SELECT * FROM table” );
$num_rows = mysql_num_rows( $result );
$index = rand( 0, ($num_rows-1) );
$random_record = mysql_result( $result, $index );