MySQL Storage Engines (Part 2)

Below are remaining mysql storage engines as discussed in part 1

BDB
Sleepycat software provided mysql with Berkeley DB(BDB) transnational storage engine
activated in MySQL-Max binary distributions
have great chances of surviving crashes and also capable of COMMIT and ROLLBACK operations
It require to be patched before using this with MySQL
EXAMPLE
added in 4.1.3, it is stub engine that does nothing
its purpose is to server as an example in MySQL source code
tables definition file <table_name>.frm
Does not support indexing

FEDERATED
Added in 5.0.3, access data in tables of remote databases rather than locally held tables
when FEDERATED table is created it only creates definition file i.e. <tablename>.frm because actual data is present in remote database
to read results rows fetched one at a time by using mysql_fetch_row function.
basic flow is
SQL calls issued locally
MySQL handler API (data in handler format)
MySQL handler API (data converted to SQL calls)
remote database->MYSQL client API
convert results set to handler format
Handler API->Results rows affected count to local

ARCHIVE
It was added in 4.1.3, used to store large amount of data without indexing with very small footprint
When ARCHIVE table is created server creates definition file in db directory i.e.<tablename>.frm
Data storage engine creates several other files all with name beginning with tablename
Data file <tablename>.ARZ
Metadata file <tablename>.ARM
File created during optimization operations <tablename>.ARN
Only support SELECT(complete table scan) and INSERT(records are compressed)
Use of OPTIMIZE TABLE can analyze table and pack it into smaller format

CSV
was added in 4.1.4, stores data in text files using comma separated value format
when CSV table is created server creates definition file <tablename>.frm
storage engine also create data file <tablename>.csv
data file is plain text file.

ENGINE Vs TYPE
two different keywords to archive same thing.
ENGINE – introduced in 4.0.18
TYPE- Before 4.0.18 from 3.23.0

TRANSACTION-SAFE(TST) v/s NON-TRANSACTION-SAFE(NTST) Tables
TST have advantages over NTST
Safer in cases of crashes or hardware problems.
can be recover either by automatic recover up plus the transaction log
number of statement are accepted as combined during COMMIT
ROLLBACK can be executed (if no AUTO COMMIT)
if update failed all changes will be restored
better concurrency
NTST have several advantages on its own
no transaction overhead, much faster
lower disk space requirements
less memory require to preform operation

go to part 1