Page 1 of 1

Table with no primary key ?

Posted: Sat Nov 12, 2011 3:32 pm
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!

Re: Table with no primary key ?

Posted: Sat Nov 12, 2011 4:06 pm
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?

Re: Table with no primary key ?

Posted: Sat Nov 12, 2011 4:07 pm
by Christopher
Is the I'D column in the error table indexed?

Re: Table with no primary key ?

Posted: Sat Nov 12, 2011 4:10 pm
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?

Re: Table with no primary key ?

Posted: Mon Nov 14, 2011 1:15 am
by Christopher
What kind of database is it?

Re: Table with no primary key ?

Posted: Mon Nov 14, 2011 10:14 am
by J0kerz
An SQL Database (mysql) .

Re: Table with no primary key ?

Posted: Mon Nov 14, 2011 6:04 pm
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

Re: Table with no primary key ?

Posted: Fri Nov 18, 2011 2:02 pm
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 :)