将`INT`主键打包成一个`LONG`

Question

Is it efficient to "pack" two INT primary keys (a composite key) into a single BIGINT primary key? Can it be done automatically within mySQL or does it need to be done in the client (PHP, C++)?

I'm creating a directional graph connecting user IDs to one another (e.g. for a friend-finder) in mySQL.

MySQL supports 8-byte BIGINTs and 4-byte INTs, so the packer might look something like:

id_edge = ((BIGINT)id_from)*pow(2,31) + ((BIGINT)id_to)

How do the big boys (FB, LI) do it? Is it easier or better to have three "independent" fields:

CREATE  TABLE `things`.`connections` (
`id_edge` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Need a single pk for gii
' ,
`id_from` INT NOT NULL ,
`id_to` INT NOT NULL ,
....

Answer 1

No. You shouldn't be seeking to micro-optimise the storage. Each column should store 1 piece of data - no more and no less.

If you implemented the packing you propose, you'd make indexing impossible - a full table scan would be required to find e.g. all rows with id_to equal to 19.

---

You can also read Chapter 13 of the MySQL manual, which discusses the various storage engines and how they actually store table data.