MySQL: Open Source Solution for Data Warehousing

At Forum One, MySQL is the open source database that powers most of our internet solutions. It's flexible, powerful and free. Our confidence extends to one one of the more complex and growing needs facing many of our clients: Data warehousing.

Data warehousing is a method for storing organizational data electronically with the intention of extracting it using business intelligence tools for sophisticated analysis and reporting. The market for data warehousing has been increasing every year. More businesses are running enterprise applications that collect valuable information, and larger nonprofit organizations are taking advantage of it as well. (TechSoup has a useful overview of how data warehousing can benefit an organization's operations.)

As organizations embrace these tools, they seek scalable and less expensive software tools. This is where MySQL becomes a good choice.

Use Cases for Data Warehousing

Here are the most common uses we see:

• Real-time data warehousing
• Persistent resource between incoming data and already-stored data
  
• Data that is not active hourly or daily. This is the most widely used case for most businesses. It will store very large amounts of data.
• Historical data being stored in multiple areas. The data can then be used at a later time.
  
• Data is stored in large amounts of data in multiple warehouses. This type has less query traffic than Real-Time and Traditional warehouse setups.
  

Some organizations will use more than one of the use cases above. This will result in many instances of a database and servers in use, and increase costs. Open source Relation Database Management Systems such as MySQL are the best solution for most businesses looking to reduce the cost of data warehousing.

MySQL's Data Warehousing Features

Given the use cases I list above, MySQL offers a number of advantages:

• Data/Index partitioning for versions 5.1 and above
• Large table space. A single table can be as large as 110 terrabytes
• Replication
• Variety of indexes (B-tree, full text, clustered, hash and GIS)
• Data compression
• Read-only tables for sensitive data
• Support for multiple operating systems

MySQL's Storage Engines

MySQL is flexible when it comes to choosing a data warehouse engine. Any of the MySQL engines can be used, but there are a few that are best for the job. The ones listed below are the best choice: 

• MyISAM
• Archive
• Memory
• Merge
• Federated

Let's consider each of these engines in turn.

MyISAM 

The MyISAM engine is the default engine of MySQL and can be a good choose because of high speed query inserts. If offers table level locking, but not row level locking.

Archive 

The archive engine compresses data up to 80 percent and is a good option for large amounts of data storage. It offers good table scans and has row-level locking. The archive engine only allows data to be inserted and read. The engine does not allow for updates or deletes. This is a great option for storage of sensitive data. 

Memory 

The memory table is extremely fast and has fast response time for full-table scans. The data is stored in the system memory and not on disk. It is very important that the data in memory is backed up. If the system goes down, then all data in memory is lost. 

Merge 

The merge table is great for data partitioning in MySQL versions 5.1 and above. The database administrator can merge one or more tables. Each table can be stored on separate systems.

Federated 

This type of table, allows database managers to create many databases on different servers. The data can exist on the local server or remote server. 

MySQL's External Storage Engines 

NitroEDB

Nitrosecurity and MySQL partnered together and developed the NitroEDB external database engine. The main benefit from this, is the enormous amount of data that can be stored. The engine also provides concurrent queries.

Brighthouse 

Another partnership with MySQL is Infobright, the producers of Brighthouse. Brighthouse improves data compression by using its own optimizer and not MySQL's optimizer. Brighthouse improves compression because the engine stores data in a column style and not row by row. With its column-based approach, Brighthouse can store massive amounts of data. 

Conclusion 

The growth of MySQL has grown in tremendous strides for the past few years. There are many enterprise features, that can be implemented to solve most needs for an organization. 

In today's world, companies are looking to cut cost and also preserve existing technology investments. MySQL provides a lot of power at low cost. This makes it an attractive alternative to many other enterprise solutions.