Performance question - session vars vs. mysql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cmega
Forum Newbie
Posts: 4
Joined: Wed May 28, 2003 9:28 am

Performance question - session vars vs. mysql query

Post by cmega »

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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

I was just thinking; Why not both ways?

I also got stuck on the line reading: '...or is it more efficient to query mysql for the info each page?'. If it's efficient or not, I'm not 100% sure, but storing everything in session variables sounds like a potentional memory/cpu hogging solution.
Personally I've never thought of using sessions as this type of storage container so I might be way of.

Is there a certain reason to why you want to skip the database data retrieving part of the script?

(Interesting subject, code bits or .phps would be welcome also to further explore ;))
cmega
Forum Newbie
Posts: 4
Joined: Wed May 28, 2003 9:28 am

Post by cmega »

Hi JAM-
your question: "Is there a certain reason to why you want to skip the database data retrieving part of the script?"

The reason is simply that the information being retrieved is not likely to change often (perhaps once every 2-4 weeks). Therefore every query for a particular ID is pulling the same information it retrieved the previous time. This seems wasteful.

The session variable would become the cache for the query. If the data becomes stale due to a db update, that's OK, because the next new session will get the new info. I'm not concerned with stale data given the nature of the data.

Your comment about the potential memory/CPU hog situation is the basis of my concern. Is it more expensive (CPU/memory) to store large session variables, and have the PHP engine parse them out for every page in my site? Or is it more expensive to store a small session variable (still requires PHP parsing) but rely on the mysql engine to retrieve 'known' information for every page in the site?
(This is kind of like the woodchuck issue - does a woodchuck actually chuck wood? Noone seems to know :wink: )

- Chris
Post Reply