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
Advertisements

, , , , , , , , , , , , , , , , , , , , , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: