I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates which can be very expensive.
Today I would like to share practical example when you may use long primary key with value distribution far from sequential.
For one of the projects I had the task to store meta data about thumbnails for images retrieved from various web sites in the Internet. Thumbnails themselves were stored on the disk of course but meta data such as original image sizes, file size as well as thumbnail location on the disk were stored in the database:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `imagecache` ( `url` varchar(255) character set latin1 NOT NULL default '', `thumb_width` smallint(5) unsigned NOT NULL default '0', `thumb_height` smallint(5) unsigned NOT NULL default '0', `width` smallint(5) unsigned NOT NULL default '0', `height` smallint(5) unsigned NOT NULL default '0', `size` int(10) unsigned NOT NULL default '0', `pathversion` tinyint(3) unsigned NOT NULL default '0', `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height`) ) ENGINE=InnoDB; |
Why did I use this solution compared to others:
Innodb Tables – This table is getting much more reads than writes so transactional overhead of for writes is small price to pay for number of benefits – caching data and index in memory -so cached lookups are very fast, clustering by primary key – so for disk bound lookups date is retrieved with 1 IO not with 2 IOs as with non-clustered tables. The other benefit – it is typical to show multiple thumbnails from the same album/domain, and due to clustering there is high probability it will all come from one or few close pages, saving IO dramatically. No recovery worries – checking/repairing large MyISAM tables in case of MySQL/System crash is painful and great to be avoided.
Long primary key – why did not I use auto_increment id in this case ? I could but this would kill clustering benefits described above. Also as this is only index I have I do not have any overhead as this would come only if extra indexes are defined. The primary key itself does not get much larger whatever columns you place into it, as primary key BTREE contains all table data in leafs anyway. Top level pages can get a bit larger for long primary keys but usually it is not major.
Non Sequential primary key This just comes as an effect of choosing url as the key. It surely makes inserts slower as they are random and page splits have to happen. Full table scan for this table would also be slow as it is quite fragmented. Table however is not getting any table scans just single row lookups by primary key. Also in practice value distribution is not as bad in this case as if I would decide to use md5(url) or something similar as a key – it is often many images from one domain/album are inserted at about same time which makes access locality much better.
What thumb_height and thumb_width are doing in index ?
In this case we could have multiple thumbnail sizes for the same image which is why they were added. We also wanted to keep system flexible so if we want add more thumbnail sizes we would not need to change anything. Why are they going after the url rather than before it in the index ? They are not selective plus we might want to get all thumbnails for given image url effectively.
In general I use this example as illustration no recommendations are good for every case and you need to check what is important in your case rather than accepting general recommendations without giving much thought on which assumptions they are based.
P.S Yeah in theory URLs can be longer than 255 characters but it was not important in this case.
