These are some of the neater parts of gallery.chaostangent.com that don't warrant a full exploration on their own but serve the goal of making the application more streamlined. I've crafted these examples to be focused so they don't contain superfluous details like error checking, timestamp columns and the like.
Database
The gallery schema is as follows:
CREATE TABLE IF NOT EXISTS `galleries` (
`id` int(10) unsigned NOT NULL auto_increment,
`left` int(10) unsigned NOT NULL default '0',
`right` int(10) unsigned NOT NULL default '0',
`parent` int(10) unsigned NOT NULL default '0',
`title` tinytext NOT NULL,
`directory` tinytext NOT NULL,
PRIMARY KEY (`id`),
KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
This covers both the Modified Preorder Tree Traversal (`left` and `right` columns) model as well as the more standard hierarchical model (`parent` column). I'm still undecided as to whether indexing the `left` and `right` columns provides any benefits. Most of the queries on the gallery table involve getting the direct children of a particular node; the breadcrumb trail at the top of the page however is built using the `left` and `right` columns:
SELECT * FROM `galleries` WHERE (`left` >= ?) AND (`right` <= ?) ORDER BY `left`
Doing a multi-column index in MySQL works from the left column onwards, so for the above query, indexing on `left` and `right` would be a benefit. However when inserting and deleting nodes, queries are done singularly e.g. one for `left` and one for `right` which having an index on one and not the other may turn out to be detrimental in terms of update times. I could always do two indexes:
ALTER TABLE `galleries` ADD INDEX ( `left` , `right` ) ;
ALTER TABLE `galleries` ADD INDEX ( `right` , `left` ) ;
This runs the risk though of having a table that's more index than data. I haven't done a full benchmark of the different queries for each scenario but I would imagine only for large trees would indexing provide any tangible benefit.
Read the rest of this entry
