why setting userid be primary key?

[tonald]

why setting userid be primary key?
User_name is also unique.
Thank

[famulus]

User names may need to be changed at some point.  ID's wouldn't.

[JGadrow]

Actually, this shouldn't be the primary reason. I can hear you already, "But! Changing the primary key would break the association of data!" That's why foreign keys exist.

If you define a table as having a foreign key that points to the primary id and tell it to update whenever that primary key is updated, then this line of reasoning is defeated. This is, however, usually defeated because most people use MySQL as their database of choice and only worry about getting it installed without worrying about what options should, perhaps, be changed.

For instance, MySQL allows for foreign key constraints. But ONLY if the table engine is set to InnoDB instead of the default MyISAM.

No, the *real* reason for making user_id is because it is easier for the database to find an integer-based index than a string-based index. Thus, it's a performance optimization is all. Personally, I prefer to degrade performance a little in favor of eliminating the unnecessary data. I like how foreign key constraints help the program carry out its logic.

Need to delete a user? Just delete the user entry. The database will handle deleting all the crap that comes along WITH the user (session data, game facilities, avatars / profile information, even message posts if you so desire). Performance optimization can be made up (if you need it) by throwing more servers together and load balancing them. This also helps provide you with redundancy in case some of your hardware fails so you don't lose everything in that scenario.

[JGadrow]

From a PM to me by famulus (in case others misconstrue my intent):

I saw a post in broken english and figured I'd throw out a quick answer.  Your reply was aggressive and showed more than a little contempt.  Is this what I should expect here?  Do I need to go over every post to make sure it's 100% perfect?  Do I get to go over yours and attack you if you aren't?  Don't get me wrong.  I'm more than happy you corrected me.  We ALL have something to learn from each other, but courtesy won't hurt.

I intended no disrespect. Upon reading my original post, I'm sure the line you're upset over is when I had the quote. It was meant to be over the top and sound whiny. I was NOT using the word 'you' to refer to any particular individual, but to the fictitious masses who WOULD say something of the sort. Then, I gave my reasoning behind that.

Famulus, I would like to publicly apologize that you misread the intention of my post as an attack on you. As you can see, that's not my style. I didn't state you were wrong to use integers for primary keys, in fact, I gave good reason to do so. Afterwards, I stated why I, personally, defy that convention when it's feasible. I didn't mean for my attempt at humor to insult you or anyone else who shares your view.

I just try and interject a little light-heartedness into my posts so I don't sound like some pompous academic programmer

[Mufasa]

Makari,

I've done very little dabbling with INNODB table type (I realized in the application I was building, the pluses for using it didn't come close to outweighing the minuses). I didn't know that other tables were updated when a row was deleted (based on foreign keys).

Though I could see it causing unintentional errors/data loss, yhat's extremely useful. Especially for me, since I usually forget a table or two to clear when writing delete functions.

[tonald]

User names may need to be changed at some point.  ID's wouldn't.

Sorry for my broken english.

[famulus]

Sorry for my broken english.

Don't be.  English isn't everyone's first (or second) language and it's entirely reasonable.  My thinking was that a question in broken english may be less likely to get a response so I wanted to make sure you got one, even if it was only a quick one.  Makari followed along with an even better one.

[codestryke]

One waring with INNODB, it's very, very slow when doing SELECT COUNT(*)

We had a message center that was using INNODB and doing a

SELECT COUNT(*) FROM mailboxes WHERE isRead = 0 AND playerid = ?

This query, because it was accessing an INNODB table, caused the game to lag quite a bit (albeit the game players send and save a LOT of messages). The table itself wasn't all that large IMHO about 350k records.

[JGadrow]

The foreign keys don't AUTOMATICALLY update Mufasa, you have to specify their behavior. There are other options other than performing updates. You can set the item to null, you can DISALLOW the delete of the referenced row until the child row is deleted, etc.

And codestryke is right: InnoDB tables DO have a performance hit of their own. Don't just go converting all of your tables to InnoDB just because you can. Just like anything else in web, evaluate what is best for YOUR program and go from there.

[Harkins]

codestryke: Sounds like you needed an index on isRead and playerid... otherwise it'd probably have to fall back to a table scan which would be slow whatever the backend.

Did you run an 'explain' on it to get the query plan?

[Scion]

the select count(*) is a controversial query when it comes to storage engines.

those in the MyISAM camp say the InnoDB has poor performance with it...

however those in the InnoDB camp will tell you that most select count(*) querries are executed the same way by all storage engines.

From my point of view the transaction support in InnoDB makes it my engine of choice.

the other point to note is that MyISAM will usually work pretty optimally with the default settings...where as InnoDB can benifit substantially through proper DB and Server Administration.

[codestryke]

codestryke: Sounds like you needed an index on isRead and playerid... otherwise it'd probably have to fall back to a table scan which would be slow whatever the backend.

Did you run an 'explain' on it to get the query plan?

Yup had the index, yes ran explain on it and it was still dreadfully slow.

Like Scion said after that I started doing more research into the engines and myISAM authors claim INNODB is slower with those types of queries. Though my research I found that it's slower not because of the query execution but because of the different locking mechanism each one chose to use. myISAM uses table locking which performs better with SELECT (*), the row locking by INNODB causes it to slow down.

I'll admit even after many years of mySQL usage I am still way more comfortable using MSSQL or Oracle, funny they have row locking and there still fast has hell with SELECT (*). I'm not the best with tuning the INNODB engine but I defiantly don't use the one that comes default with the engine. I honestly can't see some magic tuning sequence that is going to make that query execute any faster, could be wrong though

For that game I ended up splitting the tables, now that triggers are a bit more stable (emphasis on a bit more) in mySQL I've moved to de-normalizing unread.

[Harkins]

Cool, thanks for the great writeup of what you tried and where you're headed.

[travo]

From my point of view the transaction support in InnoDB makes it my engine of choice.

Give ADODB a shot, if you havent already. It has functions that replicate transactions, and as far as I know thay are kinda fast (havent tried other than on my computer lol)

And you can still have the speed of MyISAM

[Chris]

ADODB is up to 3 times slower in some cases...
http://community.bbgamezone.net/index.php/topic,1362.0.html
http://adodblite.sourceforge.net/benchmark.php

As for storage engines I prefer MyISAM over InnoDB. Transactions are not vital in games and the additional speed is always nice (not to mention additional RAM required, you *need* to tweak buffer settings if you want to use InnoDB, otherwise don't even start).