SQL command

[Angelic]

I need some tutorials to show me how to optimize my SQL queries.

I managed to do everything, follow the tutorials and so on but every query I had to do by myself is pretty long and it connects to database more than one time.. with my current hosting it won't be a problem for 30 players in the same time but if there are more..

Any suggestions?

Thank you

[raestlyn]

How about a posting an example query?

[Angelic]

For example:
To use only one item (that's water) I made this entire query group (huuge)

If you could show me on this one what can I unite or delete or what queries should I use, I will fix the others :-?

function use_water() {
//Get user ID
$query = sprintf("SELECT id FROM accounts WHERE UPPER(username) = UPPER('%s')",
         mysql_real_escape_string($_SESSION['username']));
$result = mysql_query($query);
list($userID) = mysql_fetch_row($result);
//Get Item quantity for userID
$query = sprintf("select id from items where name = 'Water'");
      $result = mysql_query($query);
      list($itemID) = mysql_fetch_row($result);
      
$query = sprintf("SELECT quantity FROM user_items WHERE user_id = $userID AND item_id = $itemID");
      $result = mysql_query($query);
      list($quantity) = mysql_fetch_row($result);
      if ($quantity > 1) {
         $query = sprintf("update user_stats set value = value+4 where user_id = $userID and stat_id = $itemID");
             mysql_query($query);
           $query = sprintf("update user_items set quantity = quantity-1 where user_id = $userID and item_id = $itemID");
         }
         else
         {
         $query = sprintf("DELETE FROM user_items WHERE user_id = $userID AND item_id = $itemID");
         }
            mysql_query($query);   
}

[bbgames]

Before trying to trim down the number of queries on your page, you may want to do some profiling and see whether that's where your bottleneck is - how do you know that it's the queries, and not the overhead of establishing a connection?

If you're up for doing some server config, setting up and configuring MySQL's Query Cache may be a quick performance win - in addition to that, using mysql_pconnect will make your MySQL connection persistent, so that you don't have to open new ones every time you hit a new page.

[Nox]

1) Store ID in session too, it's should be the main value, note that ID is a unique identifier, it's nonsense to have username and through it fetch ID since everything should be done via ID
And since I think this is not the only action in the game, I suspect you have several the same queries in the whole script completely unneccessary

2) What if you want to use water for not current player? Maybe a function argument $id ... and there you insert ID of the player that should perform the action, you save a query and improve universality of the function


You can use $variable = mysql_result(mysql_query(...), 0); to get a single cell

[Chris]

Tutorial:
http://community.bbgamezone.net/index.php/topic,399.0.html

Judging from the example query you are not ready for SQL optimization yet, first you should get rid of some nasty habits. Like not storing USER_ID in session or querrying using TEXTs instead of INTs.
You probably learned from open source games or some web tutorials, these use such devastating techniques. Not sure how to get rid of such habits quickly... I would start reading carefully what Nox written below (and be very, VERY careful when reading webtutorials other than from MySQL website, at least 90% of them are anti-optimization).

Once you get past these things I recommend doing optimization in that order:
- design proper table structure
- make donation features so you can afford better hardware
- simplify the game mechanics and do not include the heavy consuming ones (the best query optimization is to remove the need of a query )
- check some stats, you will be surprised what pages are accessed the most often, then concentrate on these
- later, much later, add proper indexes (the nice things about indexes is you can change them anytime without breaking the code at all) and do query optimization.
- if you are on InnoDB make some buffer adjustments (but not too much! Too big buffer is frequently worse than too small), on MyISAM you are fine with default settings.

And above all, judge if you really need the optimization

Note on reading tutorials:
- MyISAM and InnoDB are completely different things. Most tutorials do not distinguish these and they give plain incorrect advices.

[tokyoinflames1]

Agreed with the donation feature, or even throw google ad-sense on there and offer a reward for players to click the link, giving you money from google to have better specs.

[dsheroh]

or even throw google ad-sense on there and offer a reward for players to click the link, giving you money from google to have better specs.

...or don't.  Google's AdSense terms of service specifically prohibit asking users to click on the ads.  If they were to discover that you were not only requesting ad clicks but rewarding users for clicking on ads, then no good would come of it.  At the very least, they would reverse all your ad payments on the basis that the ad clicks were fraudulent (i.e., the users weren't clicking the ads out of a genuine interest in the advertised product or service, but instead were only faking interest for the sake of gaining the offered rewards).

[Nox]

Agree, since I heard even one your own unexplained click on your ad may lead to ban I'm almost sure this may have the same consequences