Trying to prove him wrong, ( my database teacher )

Jawn · 02-15-2007, 11:22 AM

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, 11:38 AM

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, 12:48 PM

Quote:

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.
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, 01: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, 03:15 AM

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

Dan · 02-16-2007, 03: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, 05: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, 05: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, 06:13 AM

Quote:

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, 08: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, 01:04 PM

Quote:

Originally Posted by beaver6813

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

Right.

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

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

Quote:

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
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, 12:29 PM

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

Tree · 02-17-2007, 02: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, 02:49 PM

Quote:

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 :P

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

Quote:

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, 07:11 AM

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

RickM · 02-19-2007, 07: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, 08: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, 11:22 AM	#1 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 206 100.00 NP$ (Donate)	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, 01:06 PM	#4 (permalink)
TwistMyArm NamePros Member Join Date: May 2006 Posts: 160 81.00 NP$ (Donate)	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 \| CMS Book Store

02-16-2007, 03:15 AM	#5 (permalink)
JFS NamePros Regular Join Date: Oct 2005 Location: Portugal Posts: 760 56.85 NP$ (Donate)	the main diference is on size, the speed is same basically __________________ João Fernandes Silva Selling : 19P.ORG - ARCADEHITS.ORG - AZIAN.NET - COREFANS.COM - CTUTORIALS.NET - DEDISEEK.COM - HITCHECK.COM - HOST15.COM - HOSTCUSTOMER.COM - LARGETIPS.COM - SCRIPTCANDY.COM - VISUALBOOK.NET - VOXVPS.COM / .NET - WALLPAPERSARENA.COM

02-16-2007, 05:05 AM	#7 (permalink)
Barrucadu Barru. Join Date: Aug 2005 Location: East Yorkshire, England Posts: 2,731 78.50 NP$ (Donate)	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 \| Uzbl (my branch) 9thSeptember2009.com

02-16-2007, 08:47 AM	#10 (permalink)
beaver6813 NamePros Regular Join Date: May 2005 Location: England Posts: 349 65.50 NP$ (Donate)	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 V5 Soon! Last edited by beaver6813; 02-16-2007 at 01:18 PM.

02-15-2007, 11:38 AM	#2 (permalink)
netzilla Senior Member Join Date: Aug 2005 Location: Ohio Posts: 1,704 223.80 NP$ (Donate)	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, 03:42 AM	#6 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,801 56.00 NP$ (Donate)	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, 05:07 AM	#8 (permalink)
Shorty Senior Member Join Date: Sep 2005 Location: England Posts: 1,035 102.05 NP$ (Donate)	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, 12:29 PM	#13 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 206 100.00 NP$ (Donate)	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, 02:33 PM	#14 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	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, 07:11 AM	#17 (permalink)
Amnezia Professional Monkey Join Date: Jul 2005 Location: Escaped from the zoo Posts: 908 13.25 NP$ (Donate)	won't make much difference to the speed of the database __________________ [http://www.webmasterwords.com/python-split-and-join-examples]Python Tutorials[/url]

02-19-2007, 07:42 AM	#18 (permalink)
RickM Senior Member Join Date: Sep 2005 Location: Herts, UK Posts: 3,769 62.06 NP$ (Donate)	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). __________________ I am no longer a NP moderator -- please do not PM me with moderation requests! ~ VPSSpeed.com - Unmanaged VPS Hosting from $9.95 Get a FREE Mach-1 VPS PLAN with any order - Use the coupon FREEWHIZ

02-19-2007, 08:07 AM	#19 (permalink)
liam_d The original NP Emo Kid Join Date: Jan 2005 Location: Plymouth, UK Posts: 1,648 0.00 NP$ (Donate)	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.

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