Trying to prove him wrong, ( my database teacher )

Jawn · 02-15-2007, 12:22 PM

He claims that using words as a unque key in databases is more of general standard.
But how does that affect speed if the database is pretty big.
My self prefer counters as id 1 2 3 4 5 6 etc its easy much easier than using words.
Has anyone done any research on this?

Best Regards
Jawn

netzilla · 02-15-2007, 12:38 PM

If you are using the primary keys in other tables as a foreign key which is very likely given you are using normalization. I think words or letters would be easier to understand and identify. I don't think either would be faster than the other.

Jawn · 02-15-2007, 01:48 PM

Originally Posted by netzilla

If you are using the primary keys in other tables as a foreign key which is very likely given you are using normalization. I think words or letters would be easier to understand and identify. I don't think either would be faster than the other.

Im aware of the normalization rules and if you follow em straight out there wont be a problem.
Imagine you have a database with 1 million lines, one with words as primary key and one with a plain counter 12345 etc
If you want to select one row using the word primary key database, the database "server" will have no idea where to look and it will go through whole database until it finds the correct row.
????: NamePros.com http://www.namepros.com/programming/294634-trying-prove-him-wrong-my-database.html
But using the counter one it will know exactly where to look?
But i might be wrong hehe

It would be intresting if someone who has actually done a test on this to post a input

Best Regards
Jawn

TwistMyArm · 02-15-2007, 02:06 PM

If the string key was indexed, I would guess that it wouldn't be *that* much different.

However, I don't know if you can 'autoincrement' a string like you can with an integer key.

JFS · 02-16-2007, 04:15 AM

the main diference is on size, the speed is same basically

Dan · 02-16-2007, 04:42 AM

Couldn't a bigger size "potentially" slow it down?

Just make some databases and make a couple million rows and test it. >_>

Barrucadu · 02-16-2007, 06:05 AM

I'd have thought that using integers would be more of a standard - im doing databases in school now (teacher dosn't have a clue, we are using an ancient textbook for another version of the software) and we have about 3 tables in this database, all with numeric keys.

Shorty · 02-16-2007, 06:07 AM

Are we talking about column headings for tables here?

If so, all that would effect is the speed of the query manipulating the database. You would be talking about the difference of a few dozen bytes worth of data, which depending on the word size of your processor would be a matter of milliseconds.

Barrucadu · 02-16-2007, 07:13 AM

Originally Posted by Shorty

Are we talking about column headings for tables here?

No, primary keys:

Code:

primary_key | field1 | field2 | field3 | field4 | etc
------------|--------|--------|--------|--------|------
     0      | value  |  value | value  |  value | value
     1      | value  |  value | value  |  value | value
     2      | value  |  value | value  |  value | value

beaver6813 · 02-16-2007, 09:47 AM

Personally i always use integers as my primary keys simply because it makes the database smaller and makes it easier to cross-reference and use foreign keys. So if he says that most primary keys are text/words i wouldn't assume he is right.. im guessing im not the only one using integers as my primary keys... right..?

Dan · 02-16-2007, 02:04 PM

Originally Posted by beaver6813

im guessing im not the only one using intergers as my primary keys... right..?

Right.

????: NamePros.com http://www.namepros.com/showthread.php?t=294634

I sometimes make some word columns as indexes.. but my primary keys are always integers.

Jawn · 02-17-2007, 02:40 AM

Originally Posted by beaver6813

Personally i always use integers as my primary keys simply because it makes the database smaller and makes it easier to cross-reference and use foreign keys. So if he says that most primary keys are text/words i wouldn't assume he is right.. im guessing im not the only one using integers as my primary keys... right..?

Word, i personally use integers aswell but thats not the standard hehe
????: NamePros.com http://www.namepros.com/showthread.php?t=294634
The normalization standard says that your supposed to stay away from "counter/integer" way as long is its possible.
If the primary key isnt strong enough you should pick several primary keys to make it a strong identity.
This can be hard sometimes thats why people use counter/integer way, you dont have to worry about weak identities.

Jawn · 02-17-2007, 01:29 PM

I would like to put up a test on this question.
Anyone know a good scenario to use?

Tree · 02-17-2007, 03:33 PM

Set up two tables, one that uses an integer as its primary key, and another that uses random characters. Throw in 2 million rows and then query each table a few thousand times, average the results and compare.

beaver6813 · 02-17-2007, 03:49 PM

Originally Posted by Tree

Set up two tables, one that uses an integer as its primary key, and another that uses random characters. Throw in 2 million rows and then query each table a few thousand times, average the results and compare.

Thats not a bad idea for a script... i have a cunning plan

Darkneoboi · 02-17-2007, 04:29 PM

Originally Posted by Tree

Set up two tables, one that uses an integer as its primary key, and another that uses random characters. Throw in 2 million rows and then query each table a few thousand times, average the results and compare.

That sounds like a good idea, lol. I should just do that for fun. See if anything happens to my host... Of course he might get pissed at me for it.

Amnezia · 02-19-2007, 08:11 AM

won't make much difference to the speed of the database

RickM · 02-19-2007, 08:42 AM

The chances are he is referring to Access databases...MSSQL, and not MYSQL.

Teachers generally know naff all when it comes to modern technology...My IT teacher thought that for several rows in a table that you had to do each row as a singular table (She didn't know <tr> tags existed).

liam_d · 02-19-2007, 09:07 AM

You should always use a Unique Number when it comes to keys, using a word is just stupid.

It is a standard to use A number, i have been programming everday for 6 years and all scripts/programs i've ever used have numbers for the key.

02-15-2007, 12:22 PM	THREAD STARTER #1 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 202	Trying to prove him wrong, ( my database teacher ) He claims that using words as a unque key in databases is more of general standard. But how does that affect speed if the database is pretty big. My self prefer counters as id 1 2 3 4 5 6 etc its easy much easier than using words. Has anyone done any research on this? Best Regards Jawn __________________ Second place in seoworldchampionship 2007.

02-15-2007, 02:06 PM	#4 (permalink)
TwistMyArm NamePros Member Join Date: May 2006 Posts: 160	If the string key was indexed, I would guess that it wouldn't be that much different. However, I don't know if you can 'autoincrement' a string like you can with an integer key. __________________ Free Fuel Efficiency Calculator \| Buy CMS Templates

02-16-2007, 04:15 AM	#5 (permalink)
JFS NamePros Regular Join Date: Oct 2005 Location: Portugal Posts: 800	the main diference is on size, the speed is same basically __________________ João Fernandes Silva

02-16-2007, 06:05 AM	#7 (permalink)
Barrucadu Senior Member Join Date: Aug 2005 Location: East Yorkshire, England Posts: 2,689 Follow @barrucadu	I'd have thought that using integers would be more of a standard - im doing databases in school now (teacher dosn't have a clue, we are using an ancient textbook for another version of the software) and we have about 3 tables in this database, all with numeric keys. __________________ Website \| Blog Arch Hurd developer; GNU Webmaster; FSF member #8385

02-16-2007, 09:47 AM	#10 (permalink)
beaver6813 NamePros Regular Join Date: May 2005 Location: England Posts: 392 Follow @beaver6813	Personally i always use integers as my primary keys simply because it makes the database smaller and makes it easier to cross-reference and use foreign keys. So if he says that most primary keys are text/words i wouldn't assume he is right.. im guessing im not the only one using integers as my primary keys... right..? __________________ -Beaver6813.com - Web Developer Extraordinaire! Last edited by beaver6813; 02-16-2007 at 02:18 PM.

02-15-2007, 12:38 PM	#2 (permalink)
netzilla Senior Member Join Date: Aug 2005 Posts: 1,717	If you are using the primary keys in other tables as a foreign key which is very likely given you are using normalization. I think words or letters would be easier to understand and identify. I don't think either would be faster than the other.

02-16-2007, 04:42 AM	#6 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,796	Couldn't a bigger size "potentially" slow it down? Just make some databases and make a couple million rows and test it. >_>

02-16-2007, 06:07 AM	#8 (permalink)
Shorty Senior Member Join Date: Sep 2005 Location: England Posts: 1,034	Are we talking about column headings for tables here? If so, all that would effect is the speed of the query manipulating the database. You would be talking about the difference of a few dozen bytes worth of data, which depending on the word size of your processor would be a matter of milliseconds.

02-17-2007, 01:29 PM	THREAD STARTER #13 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 202	I would like to put up a test on this question. Anyone know a good scenario to use? __________________ Second place in seoworldchampionship 2007.

02-17-2007, 03:33 PM	#14 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	Set up two tables, one that uses an integer as its primary key, and another that uses random characters. Throw in 2 million rows and then query each table a few thousand times, average the results and compare.

02-19-2007, 08:11 AM	#17 (permalink)
Amnezia Professional Monkey Join Date: Jul 2005 Location: Escaped from the zoo Posts: 907	won't make much difference to the speed of the database __________________ Webmaster Words

02-19-2007, 08:42 AM	#18 (permalink)
RickM Senior Member Join Date: Sep 2005 Location: Herts, UK Posts: 3,796	The chances are he is referring to Access databases...MSSQL, and not MYSQL. Teachers generally know naff all when it comes to modern technology...My IT teacher thought that for several rows in a table that you had to do each row as a singular table (She didn't know <tr> tags existed). __________________ RickM Web Design Forums - Over 22,000 Web Designers & Programmers just like you! WSDReg - Affordable Domain Registration. Serving NP members since 2006!

02-19-2007, 09:07 AM	#19 (permalink)
liam_d The original NP Emo Kid Join Date: Jan 2005 Location: Plymouth, UK Posts: 1,693 Follow @upurtweet	You should always use a Unique Number when it comes to keys, using a word is just stupid. It is a standard to use A number, i have been programming everday for 6 years and all scripts/programs i've ever used have numbers for the key. __________________ Gaming On Linux - Because Linux is Fun!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)