Query all at once and save to session, or multiple times?

Question

I'm building a PHP/MySQL app where users can log in and search for media assets. They can also save searches and create lightboxes (collections of assets). Finally, they can create user groups.

Does it make more sense to query all at once, at log in, for the id's for their saved searches, lightboxes, and groups and save those in session vars? Or to perform the queries when they first hit the appropriate pages? I'm looking for efficiency, and the sessions seem the way to go, but am I overlooking anything?

Answer 1

Querying all at login is a bad idea. First it slows down login and 2nd it creates a static workload on your SQL server for every login.

If you query when applicable, you have a varied load. (even if you cache the results to be used later)

Also if you store results in sessions, then you are increasing the memory used for each request for that user.

There are many things that can be good for your particular site but we need more data to be able to answer that better.

Answer 2

Biggest Rule: Only put in the session what you need in the session

I don't think it would be wise at all to put everything you need in the session all at once because you'll have a lot of occurrences where that isn't needed. The user might not stay long enough to make use of all the data in there, so you just wasted time and resources putting it there.

Put your information in there on demand (when they go to each page that requires that information).

Always keep your session object neat and slim and your performance will thank you.

Answer 3

Query only what you need and when is necessary.

Short example that proves that all in one query is wrong:

If you query for login and user data and the login goes bad, the rest of the query will not be used.