Skip to Navigation
Home › Blogs › Technology ›

Database Optimization & Vertical Partitioning

Printer-friendly version
Submitted by Dan Crowder on 26 February 2008
in

After doing some poking around on how to optimize databases for quicker reads I came across a rather simple approach called Vertical Partitioning.

This approach is rather useful in taking advantage of the Query Cache as you will ultimately reduce the frequency in which the Query Cache is invalidated (blown away!).

Before going further let me explain what the query cache does. It simply stores the text of a SELECT statement along with its result set sent to the client/user upon request. For identical queries the server retrieves the results from the query cache oppose to re-parsing the request and executing the statement resulting in quicker reads.

For tables that contain static (unchanging) and dynamic (changing) data you could take advantage of vertical partitioning. Simply put, instead of creating one table that contains static and dynamic data you can use two tables that contain the static data and another that contains the dynamic data with a foreign key from the static data table. Lets look at the following example!

Un-Partitioned
 		 Partitioned
CREATE TABLE Users ( user_id
INT NOT NULL AUTO_INCREMENT,
email VARCHAR(80) NOT NULL,
display_name VARCHAR(50) NOT NULL,
password CHAR(41) NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
address VARCHAR(80) NOT NULL,
city VARCHAR(30) NOT NULL,
province CHAR(2) NOT NULL,
postcode CHAR(7) NOT NULL,
interests TEXT NULL,
bio TEXT NULL,
signature TEXT NULL,
skills TEXT NULL,
PRIMARY KEY (user_id),
UNIQUE INDEX (email))
ENGINE=InnoDB;

CREATE TABLE Users ( user_id
INT NOT NULL AUTO_INCREMENT,
email VARCHAR(80) NOT NULL,
display_name VARCHAR(50) NOT NULL,
password CHAR(41) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE INDEX (email)) ENGINE = InnoDB;


CREATE TABLE UserExtra ( user_id
INT NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
address VARCHAR(80) NOT NULL,
city VARCHAR(30) NOT NULL,
province CHAR(2) NOT NULL,
postcode CHAR(7) NOT NULL,
interests TEXT NULL,
bio TEXT NULL,
signature TEXT NULL,
skills TEXT NULL,
PRIMARY KEY (user_id))
ENGINE=InnoDB;

As a result of the extra user information being partitioned out to its own 'UserExtra' table the frequent updates will not cause the reads for the unchanging data in 'Users' table to slow down keeping the query cache invalidation for 'Users' down to a significant minimum hence consistently faster reads.