SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Session variables vs database storage in terms of speed

    I use a session variable to record if a user is logged in which I then read from page to page. However, I recently added a verification method (where a user has to confirm their account via email). I had this setup so that it checked a database entry to see if the user was verified or not. So far so good. But it seems like there may be benefits to just checking the database once per session, and then recording that outcome in the session variable, and so to forget checking the database on each page and instead checking two session variables (the user's id and the user's verification status). However, the downside is that I add yet another session variable, and I really don't understand how sessions work all that well, so I am not sure I am getting any real gains here other than minimizing database access. I'd like it if the pros and cons were made more clear.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2009
    Posts
    669
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, let me clear up sessions for you.

    A session is a SUPERGLOBAL in the form of an array - Just like any other array in PHP. That means you can access it just like a normal array. The key difference being you must use session_start() in the top (or anywhere before content causes headers to be sent) of your code to start/reopen a session. The session data is then available to every script in that users session. That means if they start on script a.php, some data goes into the session and the user goes to script b.php, the same information will be made available to script b.php. In other words it works in a similar way to any other form of memory. It's a convenient place for you to put things on a temporary basis. When the session is closed (done automatically by php) the sesssion data is written back to a session file on the hard drive. It's stored as a serialized array in the file. When you call session_start() the data is read out of the array. AFAIK, the data is only written to this file when PHP terminates, not when setting a session value in the session array - I may be wrong though.

    Using a database is similar except you use SQL to store/retrieve information and it may take slightly longer as the database engine has to sort through your tables and find the data you've specified. That said, it's a permanent way of storing data - eg the server won't clean it out like some windows machines. IT uses the same basic principle in that the data is written into a file however the database engine can perform all sorts of sorting to filter out the data that you want to retrieve.

    So, it goes like this: Sessions for temporary data, (eg products in a shopping cart) and database for anything permanent (or temporary if you write the code to clean up temp data). For anything where the user might close their browser, use the database because not all browsers will resume a session.
    I'll do anything to avoid working on my own code

    Are you using: if (isset($_POST['submit'])) ?
    IE has a bug and does not always send the value.

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Before session handling was ironed out towards the end of the lifecycle of PHP 4 databases would be used to store user session data. The process is now largely automated as tango pointed out, though there are corner cases where the database might still be used to store the session. Long term user persistence via cookies is the most common example. When load balancers become involved is another one. In any event, long term user status data must eventually find its way into the database.

    As far as speed is concerned, both session data and database storage involve disk writes, and both have caching mechanisms in the background to mitigate this issue.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    If sessions still remain a mystery to you, look in your php.ini file, work out in which temp directory the sessions are stored (clear out your old ones if you like), start your script with a new browser session, then go back to the directory and look in the temp file which has been created.

    You'll see exactly what tangoforce described but sometimes it makes the penny drop to see the text in the file. It also gives you a handle on how you can influence the file names etc.

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ShinVe View Post
    ... and so to forget checking the database on each page and instead checking two session variables (the user's id and the user's verification status).
    This doesn't make sense to me. Why would you store a value in a session and then check if it has the correct value each page load? Either is has the correct value and the user is allowed in, or it doesn't have the correct value in which case they should not have been able to log in in the first place. Storing the value "true" in the session and check every page it's still "true" doesn't help anything (unless you refresh the value from the database at a set interval, then it might help, although I'd use others means like memcache in that case)
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Guru
    Join Date
    Aug 2009
    Posts
    669
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I only set one thing: The users id:
    $_SESSION['userid'] = $UserId;

    How do I check if they're logged in?

    if (isset($_SESSION['userid']))

    Job done.
    I'll do anything to avoid working on my own code

    Are you using: if (isset($_POST['submit'])) ?
    IE has a bug and does not always send the value.

  7. #7
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all of the responses. It does help quite a bit. I looked at the actual session files and it was pretty informative. Most are very small, under 500 bytes. I didn't understand the syntax in them (I am not sure what the pipeline does), but the values were all there that I expected to be there.

    I check two indexes in session because I want to 1) know if the user is logged in, and 2) know if their account is confirmed. So I check something like $_SESSION['userid'] and $_SESSION['confirmed'] to make sure the user's account is confirmed so that the user can access certain page features (on my site, an unconfirmed user can still do limited things).

    All of this is mirrored in the database, so I have a userid column and confirmed column in the same table. However, on each page I check isset($_SESSION['userid']) and now isset($_SESSION['confirmed']. However, the other way of doing this is to check for their userid, then do a database query (example: SELECT userid, confirmed FROM users WHERE userid = '{$_SESSION['userid']}') and then, if it evaluates true, allow the user to access the features of the page for confirmed users. The latter seems less efficient as far as coding (it requires more code) but I am also concerned about database speed and storage speed as well. I am hoping that I am not going to run into any gotchas once the site balloons that would force me to re-do something so basic and integral to the site. For the most part, I am building a stock of re-usable code right now to use across different sites (some of which will use memcache but also potentially load balancers).

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    The session idea still does not make sense. Allow me to explain.

    The 'confimed' value you store in the $_SESSION is just a snapshot of the value 'confirmed' in the database for the user, and it does not change when the value in the database changes.
    So a user logs in, and let's say he is confirmed. So we set $_SESSION['confirmed'] to true. Then on every page load we check the session and we find, obviously, that it it true (even though it was set to false in the database).
    On the other hand if the user was not confirmed, we would set $_SESSION['confirmed'] to false, and then every time we check find false as the value (even if it was set to true in the database).
    So, since the session never changes, you are looking for change in a value that will never change, so the whole check is completely superfluous.

    Now with the database approach this is different, because there the value actually can change, and the check actually makes sense.

    If you were don't want to use the database approach, I'd just check the 'confirmed' value once and then be done with.

    So something like:

    PHP Code:
    if (isset($_POST['username'], $_POST['password']))
    {
      
    $user musqli_fetch_assoc(
        
    mysqli_query(
         
    sprintf(
           
    'SELECT id, confirmed, password FROM users WHERE username="%s" AND password="%s" LIMIT 1',
           
    mysqli_real_escape_string($_POST['username']),
           
    mysqli_real_escape_string(sha1($_POST['password']))
      )));
      if (
    $user['confirmed']) {
        
    $_SESSION['user_id'] = $user['id'];
      } else {
        
    //  do not log in, user is not confirmed!
      

    (not tested)

    That way you don't have to check the 'confirmed' field in the session every time, because if it were false, they could have never logged in in the first place.
    i.e., by ensuring that 'confirmed' is true on logging in, you don't need to -and it doesn't make sense to- store it in the $_SESSION and then check it each and every time.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2009
    Location
    South Carolina
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ShinVe View Post
    ... (on my site, an unconfirmed user can still do limited things).
    I am a little confused, so this question may clear up something.
    Would an unconfirmed user have a name and password for the site?
    If not, there is no need to check the confirmed status. If a user can log-in and access some pages without being confirmed, then you are using the confirmed setting to determine if a user has "supper role" (or something like that) status, and you would need to check that.
    Each day is a learning experience.

  10. #10
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    The session idea still does not make sense. Allow me to explain.

    The 'confimed' value you store in the $_SESSION is just a snapshot of the value 'confirmed' in the database for the user, and it does not change when the value in the database changes.
    So a user logs in, and let's say he is confirmed. So we set $_SESSION['confirmed'] to true. Then on every page load we check the session and we find, obviously, that it it true (even though it was set to false in the database).
    On the other hand if the user was not confirmed, we would set $_SESSION['confirmed'] to false, and then every time we check find false as the value (even if it was set to true in the database).
    So, since the session never changes, you are looking for change in a value that will never change, so the whole check is completely superfluous.

    Now with the database approach this is different, because there the value actually can change, and the check actually makes sense.

    If you were don't want to use the database approach, I'd just check the 'confirmed' value once and then be done with.

    So something like:

    PHP Code:
    if (isset($_POST['username'], $_POST['password']))
    {
      
    $user musqli_fetch_assoc(
        
    mysqli_query(
         
    sprintf(
           
    'SELECT id, confirmed, password FROM users WHERE username="%s" AND password="%s" LIMIT 1',
           
    mysqli_real_escape_string($_POST['username']),
           
    mysqli_real_escape_string(sha1($_POST['password']))
      )));
      if (
    $user['confirmed']) {
        
    $_SESSION['user_id'] = $user['id'];
      } else {
        
    //  do not log in, user is not confirmed!
      

    (not tested)

    That way you don't have to check the 'confirmed' field in the session every time, because if it were false, they could have never logged in in the first place.
    i.e., by ensuring that 'confirmed' is true on logging in, you don't need to -and it doesn't make sense to- store it in the $_SESSION and then check it each and every time.
    Thanks for bearing with me here. I think I may have missed specifying the connection between the database value (where a user is confirmed or not) and the $_SESSION['confirmed'] value. The user receives $_SESSION['confirmed'] (set to either true or false) depending on what's in the associated db column. To give an example, a user logs in (or uses a cookie to log in automatically) and then the user's confirmation is checked at logged in (just once through a db query). The user's $_SESSION['confirmed'] is then set appropriately. Since I need to allow for both confirmed and unconfirmed users, I don't believe I can just rely on $_SESSION['userid'].

    The alternative is check to check the confirmed field in the db for that user on every page (based on their userid) to see if they are confirmed. The choice here (or at least as I see it) is between setting a session variable for confirmation once at login, and then checking that variable on every page, or doing a db query on every page based on the user's id to confirm them.

    So something like this:

    PHP Code:

    if (isset($_SESSION['userid']))
    {
      
    // user is logged in
      
    if (isset($_SESSION['confirmed'] && $_SESSION['confirmed'] == true)
      {
      
    // user is logged in and on confirmed
      
    } else {
      
    user is logged in but not confirmed
      
    }

    vs (db lookup alternative)
    PHP Code:
    if (isset($_SESSION['userid']))
    {
      
    $sql "SELECT confirmed FROM users WHERE userid = '{$_SESSION['userid']}'"
     
    // rest of the mysql stuff here to check the value
     
     
    if $row['confirmed'] == true
     
    {
     
    // show confirmed-only page view
     
    } else {
     
    // show non-confirmed page view
     
    }

    The second way looks costlier because of the db query to check the confirmation status on every page (rather than just the login page), but I thought there might be some caveats to going with session checking on every page (and therefore filling up my sessions with more data). I hope this is clearer. Or I might be missing your point still. Sorry if I am.

  11. #11
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just looked over my post again. Saw all of the syntax errors. Please ignore them, I typed most of it in this small little quick reply box.

  12. #12
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Scallio or Bill, given my code above, do you still think I am making the error you specified?

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can do a benchmark for this.

    start a timer, make a loop in which you do 100000 times that mysql select. end the timer and see what the time difference is do the same for the solution with sessions.

    I think there will be a humongous difference probably in the order of minutes hehe. Sessions are much faster. infact i bet the database will die after 1000 queries.

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ShinVe
    I didn't understand the syntax in them (I am not sure what the pipeline does), but the values were all there that I expected to be there.
    Quote Originally Posted by tangoforce
    It's stored as a serialized array in the file.
    serialize

    Dead easy to delve into how this works/looks in practice:

    Create an array, serialize it, echo it onto the screen compare it to the original.

    PHP Code:
    $a= array('club' => 'Birmingham City''goal_difference' => 19'points' => 46 );

    var_dump$a);

    $b serialize($a);

    echo 
    $b

  15. #15
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ShinVe View Post
    Scallio or Bill, given my code above, do you still think I am making the error you specified?
    Not really, no. If people who are can confirmed can log in and do stuff, albeit less stuff than someone who is confirmed, you approach makes sense.
    Although taking this path you need to beware not to expand too much on this idea. You don't want to end up checking like 10 sessions variables every time you load a page.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  16. #16
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @cups. Didn't know about that function. Thanks. I ran your code and understood the resulting echo'd syntax but there was not a pipeline. I suppose the pipeline is meant to separate seralized arrays in the session file? I created an array within the array you presented but no pipeline appeared.

    @brense. I knew the db would have a slower access rate, but I was worried there was some other hidden problem with storing so much data in sessions that I might not foresee. Thanks though for pointing out the test.

    @scallio. Thanks. I am glad I'm not going astray, then.

  17. #17
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ShinVe View Post
    @brense. I knew the db would have a slower access rate, but I was worried there was some other hidden problem with storing so much data in sessions that I might not foresee. Thanks though for pointing out the test.
    Well ofcourse you should not store really large ammounts of data in sessions because it will then start to occupy a lot of space in your server memory. With php it's always good to keep things as small and fast as possible

  18. #18
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well that is the idea. DB may be inherently slower for small lookups, but something about sessions (unknown to me) could make things even slower overall, given enough data, or have some other negative impact like a vast amount of I/O over a db. For example, the question is at what point does session usage become detrimental over just getting the info from the db. When the session is 1kb, 2kb, 10kb, etc, and so on.

  19. #19
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think we would be talking in the order of hundreds of kilobytes.
    I cannot find any specific information on this however. But I think you can compare it to uploading a large file with php. File uploads with php are also stored in the memory before they are "really" uploaded to the server.
    So you could test this by making an upload form and see how long it takes to upload files of different sizes(use a loop to upload the file multiple times for good results) To get a comparision you can attempt to store large texts in sessions and see what happens. For example you could store large json encoded strings in sessions. Paste the string in notepad aswell and safe it to see how large the file will be (approximately). If you have access you can also find the session file on your own server and see the real size.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •