Performance question - session vars vs. mysql query
Posted: Thu Dec 04, 2003 9:05 am
Could I ask for some advice on the following...
I have a mysql database where the records do not change frequently.
During initialization I create a string containing the IDs of the records
that I'll be using throughout the session. (The IDs allow me to rotate content within the site.) When I create the string, I store it in a session variable. For example:
$_SESSION['ids'] = '1,15,22,99,200'
I also keep an index as a session variable. In this case the index
would contain a value between 0 and 4, pointing to a particular element
in the string. (Ie., a pointer of 3 means I'm looking at item '99' in the
above string.) The pointer increments and wraps as the person traverses the site.
When a new page is built I use the pointer to pull the next ID from the 'ids' session variable then query a database to pull content related to that ID. The content is probably 400-1000 chars in length, using 3 db fields.
So here's the problem - if a visitor visits more than 5 pages in my site, the pointer wraps and the content being pulled from the database is the same (we'll start querying for ID '99' many times, and the results of the query will not change).
During initialization, rather than storing the IDs in a session variable (to keep the session variable small), should I pre-query the db and store all results in a session variable (400-1000 chars times number of records), and avoid the mysql query each page visit? Ie., the session variable would (conceptually) become:
$_SESSION['morestuff'] = "id=1, field1=stuff, field2=stuff, field3=stuff; id=15, field1=stuff, field2=stuff, field3=stuff; id=22, etc..."
The session variable grows because of the fielded information from the query. I also have about 10 of these session string variables active, so the example above should be multipled by 10 to approximate my situation. The advantage is that all info I need is in the session variable, and I don't have to make any more db queries while the session is active.
When a page is built, is it more efficient for PHP to parse a large session variable into its components, or is it more efficient to query mysql for the info each page? (ie., smaller parse of session vars and 1 query; or larger parse of session vars but no query.)
I do not know how/if/when mysql cacheing is in effect.
Hopefully this makes sense. Can someone give some pointers? Is there a breakpoint where one condition is more efficient than the other?
Thank you
- Chris
I have a mysql database where the records do not change frequently.
During initialization I create a string containing the IDs of the records
that I'll be using throughout the session. (The IDs allow me to rotate content within the site.) When I create the string, I store it in a session variable. For example:
$_SESSION['ids'] = '1,15,22,99,200'
I also keep an index as a session variable. In this case the index
would contain a value between 0 and 4, pointing to a particular element
in the string. (Ie., a pointer of 3 means I'm looking at item '99' in the
above string.) The pointer increments and wraps as the person traverses the site.
When a new page is built I use the pointer to pull the next ID from the 'ids' session variable then query a database to pull content related to that ID. The content is probably 400-1000 chars in length, using 3 db fields.
So here's the problem - if a visitor visits more than 5 pages in my site, the pointer wraps and the content being pulled from the database is the same (we'll start querying for ID '99' many times, and the results of the query will not change).
During initialization, rather than storing the IDs in a session variable (to keep the session variable small), should I pre-query the db and store all results in a session variable (400-1000 chars times number of records), and avoid the mysql query each page visit? Ie., the session variable would (conceptually) become:
$_SESSION['morestuff'] = "id=1, field1=stuff, field2=stuff, field3=stuff; id=15, field1=stuff, field2=stuff, field3=stuff; id=22, etc..."
The session variable grows because of the fielded information from the query. I also have about 10 of these session string variables active, so the example above should be multipled by 10 to approximate my situation. The advantage is that all info I need is in the session variable, and I don't have to make any more db queries while the session is active.
When a page is built, is it more efficient for PHP to parse a large session variable into its components, or is it more efficient to query mysql for the info each page? (ie., smaller parse of session vars and 1 query; or larger parse of session vars but no query.)
I do not know how/if/when mysql cacheing is in effect.
Hopefully this makes sense. Can someone give some pointers? Is there a breakpoint where one condition is more efficient than the other?
Thank you
- Chris