Thursday, 1 February 2007

Preview of phpOpenTracker 2.0

In a new entry over his blog, Sebastian Bergmann gives a preview of phpOpenTracker 2.0. He says this version will be a complete rewrite utilizing the features of PHP 5.2 and MySQL 5.1.

He compares the new version with the previous phpOpenTracker 1.x where the database schema is normalized. Even simple phpOpenTracker API calls result in SQL queries that involve multiple tables. These JOINs degrade in performance when more data is stored in the database. This function to the previous 1.x version is contrary with the new phpOpenTracker 2.0 as the new database schema is denormalized and only consists of two tables. He says the denormalization in this new version should eliminate the JOIN-related performance problems and temporal partitioning of the data can further enhance it.

But he cautions that the partitioning feature of MySQL 5.1 used in this version may make this application-level partitioning with dates obsolete. He further points out a limitation that makes him doubt the usefulness of using the partitioning feature for phpOpenTracker. He explains the limitation of the implementation of partitioning in MySQL 5.1 with an example. He illustrates the maximum number of partitions possible for a given table is 1024. This means that a table that is partitioned into one partition per day can only hold data for less than three years (1024 / 365 = 2.8).

Another restriction that he points out is the verbose syntax for defining such a partition with the partitioning feature in MySQL 5.1. This limitation is followed by a code example for better understanding.