MySQL Query every page load?

Hey guys,

I have a user’s username displayed on every page of my website when they’re logged in. My script checks if the user has a current logged in session, and if so, grabs the username of their corresponding userid and displays it on the page.

This seems pretty heavy if like 500 users are logged in and navigating the site. Is there any more efficient way of doing this?

I think you need to check the session, if the login session does not exist query to the database to get user information. Otherwise, just get the information from session.

Well I don’t store the username in the session. Should I be?

Right now what I am doing is storing userid (primary key) in the session, then querying to get the username based on that userid.

You could store the username in the session, but MySQL is smart enough to cache common queries for speed, and what you’re describing probably won’t cause too much overhead, assuming your my.cnf settings are set right. Of course, using your session will improve performance a bit, and will free up some of mysql’s cache for other queries.

I think it should be ok to store username in the session. You got a good idea that can save a lot of queries for site that have high traffic. :slight_smile:

Awesome thanks a lot guys!

You also need to think realistically about other data you may need in a future. What I am going to assume is a simple select using an index isn’t going to cause much if any issue at all on the average server with average load. Unless it becomes an issue though I can’t really see how I would leave it be. Caching always makes maintenance a nightmare, introduce only as necessary.

Not sure what that’s in response to… Storing a username in the Session isn’t exactly caching. In any case, it wouldn’t make maintenance a nightmare by any means. And the caching I was talking about happens automatically at the DB level… it’s not a maintenance concern at all, except for possible tuning of performance in your MySQL configuration (which is rarely ever required).

Specifically, I’m talking about the query_cache_size attribute in your my.cnf file. When a query is run repeatedly (e.g. “select username from user where id = 1;” on every page), it will get automatically cached by MySQL. Since that query is very small, 500 of them will take up very little of your cache limit, so will have little impact on most MySQL servers with 500 concurrent users.

Still, using the Session to store the username will be slightly more efficient, and it’s a good practice to use Session variables to store all of a user’s Session state variables (including name), for many reasons.

Perfect, thank you transio

I wasn’t referring to MySQL caching but application side caching.

Otherwise, you what is said about premature optimization.

So are you referring to storing the username in the Session? I think that if you’re storing the user id in the session already, then storing both values (or even an entire “user” object) is a lot less work than retrieving id from session / then the username from db. Don’t you agree?

Its a single hit to the db using an index. Is storing it in the a session more efficient perhaps, noticeable probably not.

There’s also something to be said about proceeding in a manner that prevents or makes difficult the process of optimization when the time comes to do it. Being able to optimize requires a certain amount of planning.

Agree 100%