Should I create a child table without primary key?

Question

I'm designing a database with a child table that may contains billions of records in the future. So I try to use less fields (and smaller ones) as possible.

So I'm considering not using a primary key, as it would have to be a bigint, but I will never use it in my queries (this child table won't have ever a 1-N relationship).

Does a primary key useful for something else than query and join ? I suppose Mysql have an other system to distinguish a record from another, or am I wrong ?

Thanks a lot

Edit : my create statement

CREATE TABLE `receipt_line` (
  `id` bigint(20) unsigned NOT NULL,
  `receipt_id` mediumint(8) unsigned NOT NULL,
  `prod_id` smallint(5) unsigned NOT NULL,
  `coupon_id` smallint(5) unsigned DEFAULT NULL,
  `price` decimal(5,2) NOT NULL,
  `qty` decimal(4,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE `receipt_line `
  ADD PRIMARY KEY (`id`),
  ADD KEY `id ticket` (`receipt_id`),
  ADD KEY `id produit` (`prod_id`),
  ADD KEY `id coupon` (`coupon_id`),

Obviously, the parent table is Ticket, and I have other linked tables (product, coupon). The id field is linked to nothing. The other keys aren't unique.

Answer 1

Two things which are important in your case about primary keys in InnoDB:

1. The records are stored in a B+Tree on the Primary Key
2. Every single secondary indexes will have your primary key appended to them

Downsides of not having a single auto-increment PK:

• With a larger natural key you lose a lot of space on your secondary indexes which I see you will have a lot.
• Because the primary key is not in a continuous incremental number page splits will happen more frequently -> slower insert time and more fragmentation which again will increase your tablespace size even though the net size might be smaller.

Downsides of having auto-increment PK:

• Secondary index lookups might be slower but with adaptive hash index it is managed quite well.

Using InnoDB the rule of thumb is always use auto_increment primary key and only deviate from this rule if you explicitly want to and know why you're deviating. What you may save with that 4 or 8 bytes / row you will lose more on fragmentation and bigger index size.

The only time you really benefit from a larger composite or natural primary key is if that's the only way you query the table. If any other secondary indexes comes into play it's better to have that single column autoinc PK.

Answer 2

If the records needs to be unique, then it is up to you to either pick a composite key (multiple columns) to make them unique, or use a primary key (unique by definition). I don't think there is anything more efficient. If the concern is running out of bigint values, you won't. Knowing now that you will never query or join on this table seems shortsighted.

EDIT After More Info: If some combination of the receipt_id, prod_id and coupon_id are unique then you can just use that to establish uniqueness with a compound/composite primary key and dump the bigint id.