Maria DB Storage Engines and Alogorithms used for Indexing

Maria DB is a Open source database and is a replacement for My SQL data base after My Sql (Sun Micro Systems) has been acquired by Oracle.

Database can have multiple tables and each table will use one storage engine, and we can use different storage engines for different tables in the database.

In this article i'm discussing about the storage engines of Maria DB. There are many storage engines that supports Maria DB, and these are pluggable engines.

To see the available storage engines in maria db, execute the following query

MariaDB [(none)]> show storage engines;  (or)

MariaDB [(none)]> show engines;

And for all these storage differ from one another with the following properties..


>Storage, indexes, data types, transactions(for data reliability), locking level(concurrent access), caching, memory usage ...

Here i'm providing the some of the Maria DB engines and their details..

1. Inno DB:
>This is the default storage engine for the Maria DB
>Transaction Safe : means automatic recovery, backup, rollback can be executed
>Memory caching
>Supports Row level locking
>Indexes are B-Tree data structures (R-Trees data structure for spatial indexes)
>adaptive Hash indexes (fastest way to perform querying)
>Crash recovery


2.MyISAM:
>Execellent INSERT performance.
>Non-transaction safe
>No foreign key support
>Usage : logging
>This default storage engine in Mysql from v3.23


3.MEMORY:
>Hash indexes
>Indexes are Red-black binary tree data structures
>Not support BLOB, TEXT  data types
>Excellent INSERT and SELECT query performance
>Data stored in RAM rather than disk (the tables will empty after system restart)

4.ARCHIVE:
>Stores large amounts of data by compressing it
>Select and INSERT operations only
>No indexes

5.CSV:
>No indexes
>Not supports transactions
>Columns must be declared as Not NULL

6.FederatedX (FEDERATED):
>No data is maintained in local tables.
>Used to access data from the remote tables without using clustering technology
>Querying the local FederatedX table will fetch the data from the remote table

7.ARIA:
>Available in Maria DB only (not in Mysql)
>Good performance for quries with GROUP BY, DISTINCT as it has better caching mechanism compared to MyISAM

8.ARCHIEVE:
>No indexes
>Used to store large amounts of data (internally it will use gzip to compress rows)

9. CASSANDRA:
>Flexible scheme using Dynamic columns

10.CONNECT:
>Supports Text  (CSV, INI, FMT, XML, FIX) , Binary (BIN, DBF, VEC) file format tables


11.OQGraph:
>Transactional and crash-safe
>Uses Dijkstra's algorithm and breadth-first search

12.Sequence:
>Virtual Read-only tables


13.TokuDB:
>Transactional
>fast recovery
>Fractal-Tree indexexes
>faster INSERT
>saving space by comression (space saving upto 90%)