CODEWITHSUNDEEP
mysql
gregoff
gregoff

Reputation: 89

MySQL table structure, do I need a primary key?

I'm creating a table in MySQL using Innodb. The table will contain measurements from several locations. Roughly the data that should be stored is:

In time, there might be many million new rows every day.

My questions are:

  1. Do I need a Primary Key? For the sake of my application I don't need a PK, but the database might? If so, is it a bad idea to use BigInt?

  2. Is it a bad idea to store the time as a MySQL DateTime? Or should I use a integer to store a unix_timestamp? Does it really matter in the long run? This column will be indexed.

Upvotes: 0

Views: 236

Answers (3)

Huggorm
Huggorm

Reputation: 31

As a beginner my solution would be to use a combination of the existing columns as a primary key. So long as you use enough columns which varies enough that every row becomes unique.

In this case you might need all 3 columns to be used as primary identifiers in order for it to work out. But it would save you from having to create an entire column just for the sake of a primary since you seemed worried about space.

Upvotes: 1

Imran
Imran

Reputation: 3072

  1. You should use primary key . There have no reason to avoid primary key

Doc Says

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

  1. If you want to store a specific value you should use a datetime field otherwise you should use time stamp . For example you want update a record in future the its datatype should timestamp because timestamp will change automatic for you

Upvotes: 1

M0rtiis
M0rtiis

Reputation: 3784

  1. Yep. its better to have some id as PK. Autoincrement (BigInt is ok)
  2. Int is better for highload. Much less data to store (& index) but more logic in app (not in queries).

Upvotes: 1

Related Questions