Hey, I run a online game and every time a character reloads a page he runs following query to the server:
SELECT * FROM users WHERE username = xxxxx
Anyways, I added an index to the db. But the server admind asked me if I couldn't optimize it some more. What should I do? After all it is neccesary to have up date stats on each page...
Optimize
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Why are you calling the same query over and over on each page load? If it's simply to handle login verification, set a login token via a session variable and check to see if the session var is set. If no, redirect to a login page or other means of verification. No need to check login against the DB every page request.
Another consideration is when you do run query calls, never use '*' (select all), only select the columns you require. Performing a 'select all' is usually considered amateurish and wastes resources when all you really want is a single or maybe two or three columns. In a login situation, you really don't require the user's data, only a COUNT on the table to verify that the user exists.
Another consideration is when you do run query calls, never use '*' (select all), only select the columns you require. Performing a 'select all' is usually considered amateurish and wastes resources when all you really want is a single or maybe two or three columns. In a login situation, you really don't require the user's data, only a COUNT on the table to verify that the user exists.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Optimize
First off, this single query is not going to be that resource intensive. The way you are doing this is a way that I do plenty of user sessions handling queries. In some cases I'll have upward of 30 queries in a single script, with a few being similar in style to yours (user configs, site configs, etc).Terriator wrote:Hey, I run a online game and every time a character reloads a page he runs following query to the server:
SELECT * FROM users WHERE username = xxxxx
Anyways, I added an index to the db. But the server admind asked me if I couldn't optimize it some more. What should I do? After all it is neccesary to have up date stats on each page...
Maybe ask the DB Admin what he means by optimizing more. With the exception of selecting only needed columns, the query you have is pretty basic and pretty easy for the db server to handle.