Effective Data warehousing

techie | June 7 - 2010

Data warehousing applications do not typically process small requests but rather massive queries across specific dimensions such as time, geography, customer, product, etc. These queries may require seconds, minutes, or even hours of processing time depending on database software and hardware capabilities.MySQL’s has got  various storage engines.Apart from this, the MySQL database server contains a number of core features that enable data warehousing. These include:

* Data/Index partitioning  is done by  using  composite partitioning(range, hash, key, list, composite) in MySQL 5.1 and above
*  Strong indexing support (Clustered,B-tree, full text, hash )
* Multiple, configurable data/index caches
* Pre-loading of data into caches
* Parallel data load
No practical storage limits (1 tablespace=110TB) with automatic storage management
* Built-in Replication
* Multi-insert Data Manipulation Language(DML)
* Read-only tables
* Cost-based optimizer
* Wide platform support
Unique query cache (caches result set + query; not just data alone)

MySQL’s pluggable storage engine architecture makes one to control specific engines to improve performance This storage engine architecture reduce data storage costs, and it is able to meet compliance specifications.MySQL does this with both internally developed storage engines and externally developed engines built by MySQL partners.

Some of MySQL’s internal storage engines that enable data warehousing include:
MyISAM ,a high performance non transactional storage engine originally developed for data warehousing applications. MyISAM is the default MySQL storage engine and the one most commonly used by MySQL customers for their data warehouse & reporting applications.
Archive: Government compliance  laws have forced many corporations to retain heavy volumes of historical information on their database servers. Companies are searching for ways to reduce the amount of space this type of seldom-referenced data consumes. MySQL’s Archive storage engine automatically compresses table data down to a very small size. They give typical reductions being 15 to 20 % of the data’s original size.
Memory: The memory engine is ideal for data warehouse dimension tables. All data is kept 100% of the time in main memory.
CSV: Allows flat files in comma delimited format to be instantly loaded into MySQL and referenced via SQL. Data in CSV tables can be edited via normal DML as well as through external file editors.
* Merge: The merge engine allows for tables to be grouped into a single logical unit and accessed/inserted into as one object.
Partner Storage Engines
There are special storage engines that are purpose built for analytical data,data warehouses and ecommerce portals. Some times it may read intensive envrironment. The column oriented and modular scale up out architecture makes sure the fastest possible response times.
Some storage engines delivers high performance on complex queries across terabytes of data for analytic data warehouse solutions. High speed data loading, advanced optimizer and 10:1 data compression are their salient features.