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.
receipt_id
,prod_id
andcoupon_id
. It looks like what you're getting at is this being a compound primary key. Are you asking whether you need to add a surrogate key?receipt_id
,prod_id
, andcoupon_id
won't be unique. And I don't think I need a unique key, because I won't use it in any of my query. So I wan't to know if the primary is essential for mysql itself.SELECTs
. From them, we can deduce what indexes are important. More info.