This is part one of a series of posts we will be making over the next couple of weeks, to flesh out the detail of some of the changes that are a part of the next release of Zen Cart.

In this post we will be looking at the changes to the Database infrastructure.

Database Abstraction Layer and Sql Cache

The abstraction layer has been re-factored to include a new factory based driver layer. The driver layer (the part that actually talks to the database) has been kept as light as possible. This will mean developing driver layers for other databases should be extremely easy. To highlight the new driver layer we will be including preliminary support for mysql innodb tables. Using innodb tables will also allow us to include support for transactions. Transactions are a way of ensuring that changes to the database that affect more than 1 table, do not leave the database in a corrupt state, if those changes fail at any point.

The sql caching layer has also been re-factored, to make it easier to add custom caching solutions. Again, to highlight this, we will be adding support for caching using the extremely popular Memcache system. Memcache is a memory based caching system that has the potential to provide blistering performance.

To further aid those that might wish to use other database types, postgres, oracle mssql etc., we have also created a new database layer called the DAO (for database access objects) These are simply php classes tasked to build and execute sql queries related to a specific table. The DAO's can be partially or fully overridden so that SQL can be customised easily for other database types.

Database Performance Enhancements.

Zen Cart provides huge flexibility in the options it allows shop owners. Extremely flexible product attributes, differing product types and a plethora of product marketing options. In the past this flexibility has had a downside in the number of sql queries that were used to support all of these options.

We have spent a lot of time analysing those queries, the internal database structure, and the infrastructure that supports the execution of those queries, and as a result have implemented a number of changes that seriously improve the database performance.

The first is the use of MPTT (modified pre-order tree traversal or sometimes called nested set) to describe the category structure. The original system relied on recursive functions to generate a category tree and as such could generate a very large number of queries for all but the simplest of category structures. MPTT allows us to build a category tree with a fixed number of queries no matter the size or complexity of the category structure. MPTT does have some downsides, most important of which is the complexity of the sql required to administer the category structure (adding/deleting/moving categories). However we believe that the performance gains, where it matters, to the users/shoppers experience are well worth it.


Secondly, in previous versions of Zen Cart using all of those wonderful options, like attributes, product types, product pricing options etc, took its toll in the number of queries needed to get information about the products being displayed. Each feature would run queries, without regard to the fact that some other feature may have already gathered that information.

To address this we have created new classes specifically tasked to getting all of the information relating to a product using the fewest queries possible. As mentioned elsewhere using these new classes can result in decreasing the number of queries enormously (typically 700+ fewer queries on an out-of-the-box Zen Cart install homepage, and that is with product counts turned on).

We have also increased the usage of the sql caching subsystem, so that subsequent page loads will use even fewer queries.

Finally, much use has been made of mysql optimisation techniques to improve indexing/structure to further improve performance.


To summarise

New Database Driver Layer
Extremely light/flexible drivers make it easier to support other Database Types
Preliminary support for innodb and mysql transactions

Sql Caching system rewritten
Much easier to add new caching types
Preliminary Support for Memcache

Use of MPTT for category structure
Reduces number of queries needed to 'describe' the category structure
Improves user experience thru reduced page load times

Supporting Classes to reduce query load
Hugely reduces queries needed
Reuses queries using Cache to further improve performance