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 ,
....
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.
First of all if you are going to do it with other than bitwise operators its never going to be efficient.
//assuming int is 4 bytes
$combined = ($a << 32) | $b
And even in this case it probably isnt worth the overhead.
Second is that size of int is different in different systems like 4 bytes on 32bit and 8 bytes in 64bit systems
I think MySQL already optimizes for the storage. Dont do something thats there for you for free.
Go for the simplicity or error will creep into your code.