Page 1 of 2

Storing Username in $_SESSION

Posted: Wed Aug 10, 2005 9:26 am
by Ree
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?

Posted: Wed Aug 10, 2005 9:34 am
by hawleyjr
Is your username the unique id for your table? You should always have an numeric index. It will make for much more efficient queries.

As for your question; it doesn't really matter. I would store the ID so I can do quick inserts such as:

Code: Select all

$qry = "Insert into tbl_visit(pgid,userid) values('$pg','".$_SESSION['VISID']."')";

Posted: Wed Aug 10, 2005 9:37 am
by Ree
Yes, I forgot to mention, all usernames are unique (primary key).

Posted: Wed Aug 10, 2005 9:38 am
by hawleyjr
That’s good that they are unique. But are you using them as the index as well?

Posted: Wed Aug 10, 2005 9:43 am
by Ree
I have never used indexes before, do they make query processing more efficient on DB level? I think indexes are used by the DB on its own (that is, DBMS decides whether it should use some index(es) or not), so since it seemed to be a bit of DB's bussiness I didn't put much priority on those and haven't used them yet. :)

Btw, I remember that poorly set indexes in fact may make query proessing slower.

Posted: Wed Aug 10, 2005 9:54 am
by hawleyjr
Poorly set indexes can make your query slower, that is why I said you shouldn't use the username as an index. :lol:

Think about the following two queries:

Code: Select all

UPDATE myTable set last_login = now() where username = 'hereismyusernamelookylooky@somedomainname.com'
OR:

Code: Select all

UPDATE myTable set last_login = now() where userid= 5001
In this example with over 5k records which query would be more efficient?

I recomend researching database normalization.

Posted: Wed Aug 10, 2005 10:05 am
by Ree
Yes, that would be the 2nd one. But then why would I want to set ID as index? I would set it as primary key.

Posted: Wed Aug 10, 2005 10:11 am
by hawleyjr
Yes, but if you set it up as an auto inc field. You are not only creating a numeric index but you are creating a unique id for every field.

Posted: Wed Aug 10, 2005 10:19 am
by Ree
If I use ID (integer) or a set of fields as primary key, why would I want indexes at all? A bit tricky... :)

Edit: Ah, indexes are SORTED arrays. That tells something. :) Search is always faster when data is sorted.

Posted: Wed Aug 10, 2005 10:25 am
by hawleyjr
Set your index as the primary key.

Code: Select all

CREATE TABLE `tblpagehit` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `visitor_id` int(11) default NULL,
  `pg_id` int(11) default NULL,
  `tmstamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
)

Posted: Wed Aug 10, 2005 10:29 am
by nielsene
hawleyjr wrote:Poorly set indexes can make your query slower, that is why I said you shouldn't use the username as an index. :lol:

Think about the following two queries:

Code: Select all

UPDATE myTable set last_login = now() where username = 'hereismyusernamelookylooky@somedomainname.com'
OR:

Code: Select all

UPDATE myTable set last_login = now() where userid= 5001
In this example with over 5k records which query would be more efficient?

I recomend researching database normalization.
The difference between those two queries, assuming both are indexed (and you can index text fields) is miniscule to nothing. There is no reason to promote integer surrogate keys over natural keys when a suitable natural key exists.

Posted: Wed Aug 10, 2005 10:31 am
by Ree
Interesting. Now I'll have to look at it myself, but yes, one may wonder, why do I need to create a new table field when I already have primary key.

Posted: Wed Aug 10, 2005 10:32 am
by hawleyjr
nielsene wrote:assuming both are indexed (and you can index text fields)
My assumption when I wrote that was there is no index.

Posted: Wed Aug 10, 2005 10:33 am
by nielsene
Ree wrote:Interesting. Now I'll have to look at it myself, but yes, one may wonder, why do I need to create a new table field when I already have primary key.
What was the CREATE TABLE for the orginal version of this table?

Posted: Wed Aug 10, 2005 10:38 am
by Ree
Well as I told, username is unique and set as primary key. The table itself only contains 3 fields: username (unique), password hash, and 'salt' (random string used in hasing). So basically both password hash and username fields are always unique, but since username is often shorter, I used it as primary key.