MySQL’s Core Feature Set for Data Warehousing

MySQL contains a solid core feature set that is suitable for all data warehousing use cases. The following

are just some of the features in the MySQL database server that help enable data warehousing:

• Data/Index partitioning – available in MySQL 5.1 and higher; supports range, hash, key, list, and

composite partitioning. Partition “pruning” is available, which involves MySQL only examining the

partitions it needs to satisfy a particular query instead of an entire table or index. Partition

management is also supported (ADD PARTITIION, DROP PARTITION, etc.)

• No practical storage limits – for example, 1 tablespace=110TB limit

• Automatic storage management – autogrowth data files, etc.

• ANSI-SQL support for all datatypes – including BLOB and XML

• Built-in Replication – simple and easy to configure

• Main memory tables – keeps all data in-resident in RAM; perfect for dimension tables

• Support for a variety of indexes – B-tree, fulltext, clustered, hash, GIS

• Multiple-configurable data/index caches

• Pre-loading of index data into index caches

• Unique query cache – caches result set + query, not just data and therefore provides near

instantaneous response times for repetitive queries like those used in data warehousing

• Parallel data load – loads multiple files at the same time

• Multi-insert DML – allows array-style processing via normal INSERT commands

• Data compression – provides enormous storage savings

• Read-only tables – protects sensitive data

• Encryption – further protection for sensitive data

• Cost-based optimizer – eliminates need for rule-based query writing

• Wide platform support – no need for special hardware or operating systems

Support for other data warehousing features such as materialized views and other like objects will be

provided in upcoming versions of the server.