Table with no primary key ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
J0kerz
Forum Commoner
Posts: 37
Joined: Fri May 29, 2009 2:51 pm

Table with no primary key ?

Post by J0kerz »

Hi,

I have a table in my DB to store the errors that happen during the registration of a user. Multiple errors can occur during the process and the same errors can happen several time.

Initialy, I created the following tables:

Image

Since then, the DB has been growing and queries that join the errors table now take way and way more time to process. I know that all table should habitually have a primary key and I guess that why the queries are slow. In my case, the error tables does not have primary key.

What is wrong with my above tables and how should I correct the structure to improve the speed of my queries?

Thanks for your time!
User avatar
J0kerz
Forum Commoner
Posts: 37
Joined: Fri May 29, 2009 2:51 pm

Re: Table with no primary key ?

Post by J0kerz »

I could add a usless Primary key such as "Error_ID" error table just to make sure my table have a primary key but that would be stupid. I dont want to add a useless column that I wont use.

Question for experienced programmer: Do you sometime add a useless primary key column in your table just to make sure your table actually have a primary key?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Table with no primary key ?

Post by Christopher »

Is the I'D column in the error table indexed?
(#10850)
User avatar
J0kerz
Forum Commoner
Posts: 37
Joined: Fri May 29, 2009 2:51 pm

Re: Table with no primary key ?

Post by J0kerz »

Christopher wrote:Is the I'D column in the error table indexed?
Christopher, no it is not since I do not know what index to add there...

What index should I put?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Table with no primary key ?

Post by Christopher »

What kind of database is it?
(#10850)
User avatar
J0kerz
Forum Commoner
Posts: 37
Joined: Fri May 29, 2009 2:51 pm

Re: Table with no primary key ?

Post by J0kerz »

An SQL Database (mysql) .
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Table with no primary key ?

Post by Christopher »

Then you just add an INDEX on that column. That will speed up the queries.

http://dev.mysql.com/doc/refman/5.0/en/ ... index.html
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Table with no primary key ?

Post by VladSun »

I don't see how having duplicated records for an error (e.g. 1/59 in your table) provides ANY additional information. Maybe it's just because you didn't post the whole table structure. If, so, please post it, so maybe you can find a natural PK :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply