Page 1 of 1

Optimize

Posted: Mon May 22, 2006 12:15 am
by Terriator
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...

Posted: Mon May 22, 2006 1:38 am
by RobertGonzalez
I think, if the DB is MySQL, you can use the MySQL 'OPTIMIZE TABLE' syntax. Search the MySQL manual for your version and see if there are examples that you could use. Also, search here. I believe this question was recently asked (at least, how to optimize a table in MySQL).

Other DB's, not so sure.

Posted: Mon May 22, 2006 2:35 am
by bdlang
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.

Posted: Mon May 22, 2006 6:38 am
by Terriator
It's not just to handle a login verification. The users stats are updated all the time, hence I'm calling the database at each relod to get the updated stats. Also, the '*' is needed as I almost on every page use all the columns..

Re: Optimize

Posted: Mon May 22, 2006 8:48 am
by RobertGonzalez
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...
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).

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.