Page 2 of 2

Posted: Wed Aug 10, 2005 10:41 am
by nielsene
Almost all databases will automatically create an index for the primary key column. I know both MySQL and PostGreSQL will. So in this case, there's no issue.


Now you will want to create an index on the foreign key end of anything that referes to the primary key, but that's true regardless of if its an int or a string.

Posted: Wed Aug 10, 2005 10:44 am
by Ree
Should I set hash field as index as well? It's always unique.

Posted: Wed Aug 10, 2005 11:50 am
by nielsene
It depends.

How often do you search on it? If you never search on it, don't make an index. If the hash is never involved in a foreign key relationship, don't index it.

If you always search on username='$username' AND hash='$hash', and username is already unique and indexed, the extra index on hash would buy you nothing.

Indexes speed up selects, but tend to slow down any updating operation (INSERT/UPDATE/DELETE), so you don't want to index everything. Only those columns that are almost always used to join or search.

Posted: Wed Aug 10, 2005 4:30 pm
by Ree
Thanks, now it's much more clear to me. :)

Re: Storing Username in $_SESSION

Posted: Wed Aug 10, 2005 11:46 pm
by nielsene
Ree wrote:Is it ok to store username in $_SESSION (the username is used in login)? Or would it be better for me to add an extra ID field in my user table and store ID instead?
Back to the initial question after the long aside on DB's :)

I don't see anything wrong with sticking the username into the $_SESSION.

However there are a few things to think about. What type of session save handler are you using (default is files, some people use a DB instead). If you're on a shared host, and the other clients can vew the session files, then storing a username in the session file can make a directed attack on a given user easier.

If your sessions are in a DB or you have a dedicated server, etc, this would be less of an issue.

Some people also feel very strongly about not exposing primary keys. In general I agree; however, I do like to use username as a natural primary key of the users table. Usernames are by definition public on most sites so they are exposed.

In general, if you are worried about the "safety" of sticking values into _SESSION make sure you're either on a dedicated server (virtual machines might be acceptable if your hoster is good), or use database backed sessions.

Posted: Thu Aug 11, 2005 3:03 am
by feyd
I've always been a fan of the minimalist style approach when it comes to transmissions between pages or notes passed to components in memory: only store the minimum amount of information you require to get the rest of the information. Sure, it may take a little longer to get the data set up, but you have far less data kept in active memory usually in the end.

Granted, you have to weigh the trade-offs..