Sign in to follow this  
Alex131

Unity [web] MySQL or PHP Sessions ?

Recommended Posts

Hello to the community!

I have started making a web based game using PHP, MySQL and of course the known client side scripting languages for setting up the page and receiving content without refreshing the page.

I've come across a situation where i don't know what should be used - retrieving data from MySQL or storing the data from MySQL in php sessions when the user logs in and then proceed using the sessions (saving them on log out).

I have a table in my database for quests where every quest is described by ID, name, energy cost, experience gain, % of mastery etc. And I have another table which has columns for user id, quest id and completion of the quest.

My question is: "What should have better performance - storing the tables in php session variables or connect to the database and alter it's contents every single time the user does a quest?" I ask this because I've heard and read that MySQL should be used as less as possible to achieve a higher performance, but I don't know whether such a great deal of information for every single user who logs in won't slow down the server.

Suggestions and thoughts about probable solutions or ways to optimize the database structure are welcome. I hope that somebody who has had the same problem in the early stages of his/hers web game creation could provide information about the problem as well.

Share this post


Link to post
Share on other sites
I doubt you'd come close to seeing any limiting performance from either PHP's disk-based session system or MySQL querying, unless your game is somehow landing 100+ simultaneous hits on your server.

That being said, make it easy and just run requests to the database. No matter what, you're still reading from the disk. Since you're not using a memory-based caching system like Memcached, and PHP's session system storing data in files on disk, the next best thing is using MySQL. Reads from MySQL should be incredibly fast, as you should be indexing all columns you use in WHERE and JOIN queries.

MySQL would also scale better if your game grows in popularity.

Share this post


Link to post
Share on other sites
Quote:
Original post by Alex131
I ask this because I've heard and read that MySQL should be used as less as possible to achieve a higher performance

I highly doubt that, database engines are designed for (among other things) fast data access.

Furthermore, you'll run into the problem of synchronization. Now the state stored in your session doesn't correspond to the one in your database:

- data might be lost when the session for some reason ends unexpectedly
- you can't easily put data constraints or enforce relationships in a session objects
- shared state requested from the database by another session might be outdated
- having data in several places might make it more difficult to scale
- etc.

Not that I've never run into situations where database performance became a bottleneck even after all possible optimizations, but the scale of those systems probably aren't comparable with your game. At least not yet, but let's hope it becomes so popular it will. [smile]

Share this post


Link to post
Share on other sites
Thank you for the thoughts and solution. Seeing that I shouldn't use sessions for such information I would like to ask whether I should use them for something else apart from user authentication?

Share this post


Link to post
Share on other sites
Ahhh.... Not handy. I'll find one and post a link up here.
Just validate ALL input before processing it.
Google Poison Cookie/session and XSS you'll find an article easy enough!!!

Share this post


Link to post
Share on other sites
I validate all input before processing it via javascript for the users which support it and strip all illegal characters in my php code before I put things into my database so I think this is enough, isn't it (I'm considering adding captcha once I write the main things considering the game play)? Anyway I'll google the things you said :)

Share this post


Link to post
Share on other sites
Quote:
Original post by Alex131
I validate all input before processing it via javascript for the users which support it and strip all illegal characters in my php code before I put things into my database so I think this is enough, isn't it (I'm considering adding captcha once I write the main things considering the game play)?

Client validation is nice because it enables you to report errors without making a request to the server first. From a security point of view, it adds very little. As you already mentioned, scripting can be circumvented or might be turned off completely.

It sounds like the server side validation does the bare minimum, but it might just suffice in your case. It's impossible to point out security flaws without actually seeing the code.

Captchas have nothing to do with security. At best they prevent bot or script driven automatic requests due to the required human intervention.

Share this post


Link to post
Share on other sites
javascript is a disaster in terms of security.

Supposing you had a guestbook that validated input via javascript and I was a hacker who wanted to hack it. (All hypothetical:P)
I'd just view source and then save it to my disk. I'd then re-open it, remove the javascript validation script and put in my malicous content. It would send this malicous content back to your server with you thinking it's all nice and safe.

Use server-side validation ONLY! Try the php htmlentities() function.

Captcha is very useful. I had a site and it got spammed so bad in the couple of days I had no Captcha...

Share this post


Link to post
Share on other sites
Quote:
Original post by soitsthateasy
Use server-side validation ONLY! Try the php htmlentities() function.

Sound advice, apart from that I think you meant to write addslashes().

Even then, that's a form of input sanitation, not input validation.

Share this post


Link to post
Share on other sites
Quote:
Original post by Alex131
My question is: "What should have better performance - storing the tables in php session variables or connect to the database and alter it's contents every single time the user does a quest?" I ask this because I've heard and read that MySQL should be used as less as possible to achieve a higher performance, but I don't know whether such a great deal of information for every single user who logs in won't slow down the server.


I think this advice is merely to counter over-zealous use of the database, where you fetch and write persistant state multiple times per page view.

There *are* cases where you want to buffer such data in RAM (with APC for example), however they are far and in between.

Even highly performance optimized web apps usually [unless they are using stateful web technologies like some Java stuff] perform multiple SQL queries per page view:

- to get the key data about the current user (which is what you mean)
- to find out which URL maps to which resource/page
- additional queries to make sense of that stuff
- yet more queries to perform actual work

...and they still run fast (< 50 ms).

Share this post


Link to post
Share on other sites
Quote:
Original post by chbrules
MySQL would also scale better if your game grows in popularity.


Not quite, as you can distribute sessions across machines using memcached.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Forum Statistics

    • Total Topics
      627784
    • Total Posts
      2979036
  • Similar Content

    • By KARTHI
      Now I am making 2d Game. I want to sell the game in playstore as a paid game but I didn't know how to reach the player so please someone tell me how to make them to download quickly
    • By Ahrakeen
      Hello our team aare making the game Neo arcana a logical crisis. and w are presently searching for some graphics people to help with the models for the game.
      we are using the setting of urban arcana and the game will be a strategic roleplaying game. in so we are in need of help with the graphics.
      if you interested to aiding us with your skills let us know. we are presently working on the prologue with the aim of finishing the story of some of the things that happened back then.
      to set up the story for the main game itself where tim is now trying to regainw hat he has lost back then. And manuever in the strange world that is hidden beneath the surface of the city.
       
      We aim with the prologue done turning it into a demo to get funding . so i hope to get more help with this.
    • By Hacktion Architects
      Hey guys me and my team have made our first game! Would be awesome if you guys could download, play it and let us know what you think!

      3. 2. 1. GO! Fly through the local school grounds with three other drones vying for the fastest time. Racing past the football fields, weaving in between pillars and squeezing through tight corridors, in Drozone speed is everything.
      Race through checkpoints while keeping your eyes out for power ups that give you a big short burst of speed, but be careful not to lose control!
      And if you're having problems completing the course you will want to grab the shield power up to double your health.
      Pick your drone and start racing, will you be able to beat our fastest lap time?
      Trailer here:
      https://www.youtube.com/watch?v=tekETULy2Qk&feature=youtu.be
      Link to download:
      https://gamejolt.com/games/drozone/292176
      A game developed by a group of seven young indie devs, from Brisbane Australia, Drozone is focused on delivering the exhilaration of flying a drone in first person.
      The experience is built up around competition between players and seeing who can get the fastest time. Let us know what you’re fastest time is!
    • By Polycanic Studios

      Everybody's favorite hard hitting reality blood sport is back and is better than ever. Join your host Miss Midnight and enter the Graviators arena for a new and thrilling style of combat.
      Graviators is a 3rd person arena based brawler in which you can play online multiplayer and LAN multiplayer with up to 4 friends or test your skills in our single player mode. Choose your fighter, control your gravity and fight using each characters unique ranged and melee attacks. 

      Download now at: www.graviators.com or watch the Trailer
      Graviators is currently released but will be receiving updates to make improvements and fix any issues so please bear with us! If you want to know more about what we are up to, follow us here for regular devlogs or check out our Facebook or Twitter for updates. 
      If you want to get in contact with us, please feel free to comment here, polycanicstudios@gmail.com or send us a message on Facebook!
    • By INTwindwolf
      COMPANY AND THE PROJECT

      We are an indie game studio consisted of professional and friendly people. Additionally, we are a team of skilled artists and dedicated indie enthusiasts. Our current project is INT, developed on Unity Engine 5 for platforms Windows, Linux, and Mac.

      INT is a 3D Sci-fi RPG with a strong emphasis on story, role playing, and innovative RPG features such as randomized companions. The focus is on the journey through a war-torn world with fast-paced combat against hordes of enemies. The player must accomplish quests like a traditional RPG, complete objectives, and meet lively crew members who will aid in the player's survival. Throughout the game you can side and complete missions through criminal cartels, and the two major combatants, the UCE and ACP, of the Interstellar Civil War.
      Please note that all of our current positions are remote work. You will not be required to travel.
      For more information about us, follow the links listed below.
      INT Official website
      Steam Greenlight
      IndieDB page
      Also follow social media platforms for the latest news regarding our projects.
      Facebook
      Twitter
       
      TALENT NEEDED
      We are looking for an Animator to join the Art team to create and polish animations for the game. You will be collaborating with fellow members of the team, and follow instructions from the Project Lead and the Animation team Lead in crafting smooth, flowing animations.
      As an Animator for this project, your duties would include:
      Create rigs to be used for animations. Skin 3D models to rigs. Contribute to constructive team discussions. Attend regular team meetings.  
      REQUIREMENTS
      To be successful in this position, following requirements apply:
       
      Have working knowledge of 3D animation suites. Understand import/export requirements for Unity Engine integration. Excellent self-management skills. Excellent attention to detail. Excellent communication skills. Preferred requirement:
      Knowledge of the Unity Engine UMA character creation system would be an advantage.  
      REVENUE - SHARE
      This is the perfect opportunity to get into the game development industry. Being an Indie team we do not have the creative restrictions often imposed by publishers or other third parties. We are extremely conscientious of our work and continuously uphold a high level of quality throughout our project.
      We are unable to offer wages or per-item payments at this time. However revenue-sharing from crowd-funding is offered to team members who contribute 15-20 hours per week to company projects, as well as maintain constant communication and adhere to deadlines. Currently the crowd-funding campaign is scheduled for the year 2018. Your understanding is dearly appreciated.
       
      TO APPLY
      Please send your Cover Letter, CV, Portfolio (if applicable), and other relevant documents/information to this email: JohnHR@int-game.net
      Thank you for your time! We look forward to hearing from you!
      John Shen
      HR Lead
      Starboard Games LLC
  • Popular Now