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.
Best Answer
Two things which are important in your case about primary keys in InnoDB:
Downsides of not having a single auto-increment PK:
Downsides of having auto-increment PK:
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.