0

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.

  • They make it easier to identify specific child records. Say that a child record needs to be updated later, how will you uniquely identify it for the update statement? – Jonathan Fite Nov 16 '15 at 21:37
  • I wont update the records. I will only insert or delete using other keys. – Matthieu Nov 17 '15 at 8:03
  • It's hard to understand why you don't think you need a unique key. Please explain why your primary key isn't just the combination of receipt_id, prod_id and coupon_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? – Joel Brown Nov 17 '15 at 15:11
  • The combination of receipt_id, prod_id, and coupon_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. – Matthieu Nov 18 '15 at 14:19
  • Show us the important SELECTs. From them, we can deduce what indexes are important. More info. – Rick James Dec 3 '15 at 4:33
0

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.

  • Thanks, but my question wasn't about using an auto-increment pk OR a natural key. It was about not using a primary at all... – Matthieu Nov 18 '15 at 14:21
  • About your description of the primary key in InnoDB. How are the records stored in the B+Tree when there is no Primary Key ? – Matthieu Nov 18 '15 at 14:25
  • I think I wasn't clear on that. Sorry! In InnoDB there's always a primary key. Even if you don't specify one InnoDB will create a "hidden" one for you and the same rules applies. – Károly Nagy Nov 19 '15 at 12:30
  • Ok, that's intersting ! And how does InnoDB choose the length of this hidden key ? – Matthieu Nov 19 '15 at 14:15
  • If you have unique key(s) then the first one which only has not null columns will be used. Otherwise Innodb will create a 6 byte continuously incrementing ROW_ID (like auto_increment). Detailed info can be found here: dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html – Károly Nagy Nov 19 '15 at 14:25
1

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.

  • The records doesn't needs to be unique. Actually, they won't. I'm not afraid of running out of bigint value. I'm afraid of using a bigint, as it take a lot of space. If I'm not wrong, the difference between an int and a bigint in a 1 billion record table is 11GO. Actually, I will query or join this table, but never on the primary. There is other keys I will use for that. – Matthieu Nov 17 '15 at 8:11
  • @Matthieu show us your CREATE TABLE statement, please. Are there other columns that have UNIQUE constraints? – ypercubeᵀᴹ Nov 17 '15 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.