Query Optimization

[Haunted Dawg]

Hello i have been currently recoding my game and i came across the fetch from user's. Now i tried using this:

 

$is = mysql_query("SELECT 
						exp,username,level,userid,exp_needed,
						maxwill,will,diamonds,money,hp,maxhp,
						energy,maxenergy,user_notepad,signature,
						laston,lastip,job,brave,maxbrave,location,
						user_level,gender,daysold,signedup,gang,
						daysingang,course,cdays,jobrank,donatordays,
						email,display_pic,duties,bankmoney,staffnotes,
						lastip_login,lastip_signup,crimexp,forum_avatar,
						forum_signature,new_announcements,force_logout,diamondbank,
						rating,lottery,steps_left,busts,bails
					FROM users WHERE userid=".$userid);
$ir = mysql_fetch_assoc($is);

 

But it does not work. Bellow is my user table's row.

 

CREATE TABLE IF NOT EXISTS `users` (
 `userid` int(11) NOT NULL auto_increment,
 `username` varchar(255) NOT NULL default '',
 `userpass` varchar(255) NOT NULL default '',
 `level` int(11) NOT NULL default '0',
 `exp` decimal(11,4) NOT NULL default '0.0000',
 `money` int(11) NOT NULL default '0',
 `diamonds` int(11) NOT NULL default '0',
 `laston` int(11) NOT NULL default '0',
 `lastip` varchar(255) NOT NULL default '',
 `job` int(11) NOT NULL default '0',
 `energy` int(11) NOT NULL default '0',
 `will` int(11) NOT NULL default '0',
 `maxwill` int(11) NOT NULL default '0',
 `brave` int(11) NOT NULL default '0',
 `maxbrave` int(11) NOT NULL default '0',
 `maxenergy` int(11) NOT NULL default '0',
 `hp` int(11) NOT NULL default '0',
 `maxhp` int(11) NOT NULL default '0',
 `lastrest_life` int(11) NOT NULL default '0',
 `lastrest_other` int(11) NOT NULL default '0',
 `location` int(11) NOT NULL default '0',
 `user_level` int(11) NOT NULL default '1',
 `gender` enum('Male','Female') NOT NULL default 'Male',
 `daysold` int(11) NOT NULL default '0',
 `signedup` int(11) NOT NULL default '0',
 `gang` int(11) NOT NULL default '0',
 `daysingang` int(11) NOT NULL default '0',
 `course` int(11) NOT NULL default '0',
 `cdays` int(11) NOT NULL default '0',
 `jobrank` int(11) NOT NULL default '0',
 `donatordays` int(11) NOT NULL default '0',
 `email` varchar(255) NOT NULL default '',
 `login_name` varchar(255) NOT NULL default '',
 `display_pic` text NOT NULL,
 `duties` varchar(255) NOT NULL default 'N/A',
 `bankmoney` int(11) NOT NULL default '0',
 `staffnotes` longtext NOT NULL,
 `lastip_login` varchar(255) NOT NULL default '127.0.0.1',
 `lastip_signup` varchar(255) NOT NULL default '127.0.0.1',
 `last_login` int(11) NOT NULL default '0',
 `crimexp` int(11) NOT NULL default '0',
 `posts` int(11) NOT NULL default '0',
 `forums_avatar` varchar(255) NOT NULL default '',
 `forums_signature` text NOT NULL,
 `new_announcements` int(11) NOT NULL default '0',
 `user_notepad` text NOT NULL,
 `force_logout` tinyint(4) NOT NULL default '0',
 `diamondbank` int(11) NOT NULL default '0',
 `rating` int(11) NOT NULL default '0',
 `signature` text NOT NULL,
 `steps_left` int(11) NOT NULL,
 `busts` int(11) NOT NULL default '0',
 `bails` int(11) NOT NULL default '0',
 PRIMARY KEY  (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=91 ;

 

Everything seem's to be going on rather nice i have taken away many thing's in the user's table and will be making a new table for it. But now with that i get this error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/deadlyki/public_html/**.php on line 87

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/deadlyki/public_html/**.php on line 90

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[Haunted Dawg]

Re: Query Optimization

HaHa. I accidently deleted exp_needed from the user's table.

Unknown column 'exp_needed' in 'field list'

 

-----

Edit:

It seems as if mccode's does not have exp_needed in the user's table.

[Haunted Dawg]

Re: Query Optimization

Well that work's :).

[Floydian]

Re: Query Optimization

is that really yielding an improvement?

 

that's one of those situations where the improvement might not be worth writing out all those column names.

I counted about 50 columns listed there. now if you can reduce that to 25, that'd be outstanding ;)

[Haunted Dawg]

Re: Query Optimization

Well. In the game it is calling out all of that. It would be useless to make a new query just to call that one specific thing on a different page.

[Floydian]

Re: Query Optimization

user_notepad,signature,gender,signedup, email,display_pic,duties,staffnotes

 

That's just a few columns I picked out that, IMHO, shouldn't be needed on every page load.

 

staffnotes? email? eh, If you say you're using those on every page on your site, then great, good luck ;)

[Haunted Dawg]

Re: Query Optimization

Gender image will be displayed on every page. display pic will also be displayed on every page.

[Floydian]

Re: Query Optimization

along with staff notes, user notepad, signedup, and duties no doubt... lol

[Haunted Dawg]

Re: Query Optimization

Well. Like i said. What is 0.000001 second going to harm. When opening a new query and fetching that just for user notepad. Yes i know i can remove staff note's which i will and signedup and duties. But the user notepad can stay there.

[Floydian]

Re: Query Optimization

I think you missed my point, because you're making it for me in your last post.

My point was, writing out all those column names isn't yielding you much "optimization" over doing u.*

So, the corollary to my point is that removing as many column names as is possible makes the optimization more worth while.

Does that make sense?

 

In fact, I wouldn't be surprised if naming 50 columns out of 60 or whatever it is, is actually worse than doing u.*.

Just think of the time mysql has to spend deciding which column matches, when it could just get em all without having to match em up.

I obviously haven't benchmarked your query on your game, so without doing that, no one will know for sure. It would be ironic is naming all those columns actual penalized ya on performance. lol

[POG1]

Re: Query Optimization

 

Well. Like i said. What is 0.000001 second going to harm. When opening a new query and fetching that just for user notepad. Yes i know i can remove staff note's which i will and signedup and duties. But the user notepad can stay there.

your missing the point.

Fetching 150 fields every page load per visitor compared to 50 fields would put more load onto the server. Also, you made a post about "optimization" and your not listening from the people who know what they are at.

[Floydian]

Re: Query Optimization

If you have 150 fields in the users table, you'd prolly want to consider splitting that table into a few more tables...

[POG1]

Re: Query Optimization

yeah i know 25+ is probably enough

[codestryke]

Re: Query Optimization

 

In fact, I wouldn't be surprised if naming 50 columns out of 60 or whatever it is, is actually worse than doing u.*.

Just think of the time mysql has to spend deciding which column matches, when it could just get em all without having to match em up.

I obviously haven't benchmarked your query on your game, so without doing that, no one will know for sure. It would be ironic is naming all those columns actual penalized ya on performance. lol

The fact is that it is slower doing SELECT * then naming the fields. The most important thing about mySQL, or any database for that matter, is disk IO. The number of times the db has to go to disk slows it down. Doing a SELECT * is actually doing two queries, mySQL first must go to the disk and basically do a catalog search and grab all the field names. When you name the fields it doesn't do that compare, instead it throws what amounts to an exception when the field is not in place, which is faster.

It has been said in this thread too that notepad and some other fields are not necessary. I don't have intimate knowledge of McCodes but if the notepad field is a text field then yes that will effect performance heavily, varchar and blob too also effects performance heavily with a select statement. Thats why when you look at good forum software the message, which is normally a text field, is never with the topic thread, it's in it's own table with a primary key and a text field. Text fields are dynamic and are allocated space in 8k chucks. So when a message is longer then all the rest mySQL must go through and reallocate ALL those fields to the new field size which is very IO intensive.

Now I'll be very honest and blunt here. When it comes to a game that is going to have maybe a couple hundred accounts maybe a handful of players online the above optimizations are basically "theoretical" at best. Its only when you get into heavily traffic sites with a lot of data will you really see performance benefits from the above mentioned optimizations..

[Floydian]

Re: Query Optimization

 

The fact is that it is slower doing SELECT * then naming the fields.

Care to present some benchmarks proving that? Without some sort of evidence, I'm not inclined to just take your word for it. That doesn't mean that I necessarily think you're wrong though. I merely challenge you to back up your fact.

As for the rest of codestyke's post, I think it depends on the point of view. To the players of the game, getting pages that load even 0.1 seconds faster makes a difference.

Amazon did a study where adding 0.1 seconds to page load time actually reduced sales by 1%.

To look at it another way, Bill Gates might think my yearly income trivial, but to me, and my family, it means all the world.

[Haunted Dawg]

Re: Query Optimization

This is what i have now.

 

$is = mysql_query("SELECT 
						exp,username,level,userid,diamondbank,
						maxwill,will,diamonds,money,hp,maxhp,
						energy,maxenergy,force_logout,busts,bails
						lastip,job,brave,maxbrave,new_announcements,
						user_level,gender,daysold,signedup,gang,
						daysingang,course,cdays,jobrank,donatordays,
						email,display_pic,bankmoney,steps_left
						lastip_login,lastip_signup,crimexp
		FROM users WHERE userid=".$userid) or die(mysql_error());

[codestryke]

Re: Query Optimization

 

The fact is that it is slower doing SELECT * then naming the fields.

Care to present some benchmarks proving that? Without some sort of evidence, I'm not inclined to just take your word for it. That doesn't mean that I necessarily think you're wrong though. I merely challenge you to back up your fact.

I don't have the facilities to show this any longer. Doing a query locally on a local database with a local browser isn't going to show the "real" results of the benchmark. I could though do it all locally configure mySQL and show vast differences to skew my point, but I rather not bother. I wouldn't provide a benchmark unless it's a true benchmark that is going to show real usage, not local usage.

I suggest poking around http://forge.mysql.com/wiki/ about how to optimize mySQL they reference just what I said above in various places. I really don't know what other programming you do, hell I don't know you from Adam but do you honestly think that pulling 25 fields is going to be just as fast pulling 50 fields? Memory is memory and memory must be allocated and that takes time, period.

I've been programming databases stuff since lotus123, then foxpro, paradox, access, mssql, oracle, mysql and postgres. SELECT * no matter what database you chose has ALWAYS been slower then pulling field names. This fact was driven into my head even further when I decided to write my own database for some boxed software my company was selling. We needed to reduce costs so rolling my own mini database used less space compiled then the current db lib we were using, ah the good old days of floppies.

A little history lesson, from me to all the people making games right now. I made a game called BordelloBattles from scratch, no it wasn't a pimpwar clone. Anyways the game got Farked back in the day then we were featured in Maxim Magazine and the game hits went through the roof. It blew the hell out of my server, literally. It was then I had to really sit down and learn as many things as I could about optimization just so I could keep the game going. When you get to this point there is NEVER to much optimization, the things you do in the beginning greatly effect the future of the game. I made bad choices with some of the design this cost me heavily later with a LOT of development time to re-write things I should of never been rewriting cuz I thought "oh this would never happen". My experience isn't the norm, granted, but what if it happens to you? What if your game takes off like a wild fire, how well are you prepared. I was pushed to the limit so now I take the time to write it correctly so I don't have to watch all my buddies or come over and say "no I can't I have to work" or spend time with my family. Just something to think about.

[Guest Anonymous]

Re: Query Optimization

It's laughable really - the esteemed poster above me has pointed out that optimization is important, as have many other people around this network, yet still people perform mad queries...

Just look at the field list that killah is extracting

exp,username,level,userid,diamondbank,
maxwill,will,diamonds,money,hp,maxhp,
energy,maxenergy,force_logout,busts,bails
lastip,job,brave,maxbrave,new_announcements,
user_level,gender,daysold,signedup,gang,
daysingang,course,cdays,jobrank,donatordays,
email,display_pic,bankmoney,steps_left
lastip_login,lastip_signup,crimexp

 

A large % of these fields could easily be stripped out, for example daysold and signedup. Why both? The same holds true for multiple pairs of fields. not to mention pictures.

Look at theses tables ... full of NULLs, duplicate data and/or empty data. -- Some time spent examining the structure, learning how to aggregate data, trim data types, and index correctly has benefits way over and above simple SELECT * or SELECT f1,f2,f3.

Consider also using stored procedures. These can be optimized far better as they negate the context switching between web server and data server threads.

And if you are feeling very brave, try looking at a) how data is passed between PHP and MySQL, then look at how MySQL actually runs it's queries (a *good* query analyzer is sufficient here, there are very few people who will go down to MySQL source level).

As for benchmarks, I agree with CS, real benchmarks are far more important than non public facing machines, however, enabling profiling and EXPLAINing queries can help provide some insight here as to what may be going on.

[Floydian]

Re: Query Optimization

 

but do you honestly think that pulling 25 fields is going to be just as fast pulling 50 fields?

When did I say that?

Now if you've got 50- fields and you need 49 of them, maybe it doesn't make any difference if you use * or name each field explicitly.