MySQL is a very fast and very flexible multi-user / multi-threaded database system used most popularly for Web Applications and offers the simple startup a good platform to build from at very little cost.

Supporting the ANSI 99 query set, stored procedures, cursors, triggers, updatable views, text indexing, SSL and even Database Clustering, it has grown up from it's relatively meager beginnings.

MySQL Storage Engines

MySQL offers different ways to handle and manage your data on top of the standard collation types.  Allowing you to choose an engine that closely  fits your company needs.

So if you're a large archive house that only needs to query old records you can opt for the ARCHIVE engine or if your accessing many remote sources you can choose the FEDERATED engine.  The key here is that each one is designed around a specific daily function, allowing you to optimize your hardware  for the best performance.

Here's a look at what's offered,

MyISAM

(default storage engine, best performance overall)

  • Default install: Yes
  • Data limitations: None
  • Index limitations: 64 indexes per table (32 pre 4.1.2); max 16 columns per index
  • Transaction support: No
  • Locking level: Table

MERGE

(allows to combine a number of identical tables into one)

  • Data limitations: Underlying tables must be MyISAM
  • Index limitations: N/A
  • Transaction support: No
  • Locking level: Table

MEMORY

(stores all data in memory, if power failure, you lose it all good for quick access, calculations, rapid temp tables)

  • Data limitations: BLOB and TEXT types not supported
  • Index limitations: None
  • Transaction support: No
  • Locking level: Table

FEDERATED

(allows remote data access, combining many sources into one system)

  • Data limitations: Limited by remote database
  • Index limitations: N/A
  • Transaction support: No
  • Locking level: No

ARCHIVE

(insert & select only supported, compressed, good for logs, old data)

  • Data limitations: Data can only be inserted (no updates)
  • Index limitations: N/A
  • Transaction support: No
  • Locking level: N/A

CSV

(stores as comma-separated data, good for data transport)

  • Data limitations: None
  • Index limitations: Indexing is not supported
  • Transaction support: No
  • Locking level: Table

BLACKHOLE

(allows you to test out possible data structures, schemas)

  • Data limitations: No data is stored, but statements are written to the binary log (and therefore distributed to slave databases)
  • Index limitations: N/A
  • Transaction support: No
  • Locking level: N/A

ISAM

(original engine, included only for backwards compatibility)

  • Data limitations: Limited maximum database size (4GB)
  • Index limitations: Maximum 16 indexes per table, 16 parts per key
  • Transaction support: No
  • Locking level: Table

BERKELEY DB

(hash-based storage engine, very quick to access & recover great for accessing data that does not change much, due to it's table locking)

  • Data limitations: None
  • Index limitations: Max 31 indexes per table, 16 columns per index; max key size 1024 bytes
  • Transaction support: Yes
  • Locking level: Page (8192 bytes)

INNO DB

(based on myisam + adds database cacheing & indexing, in memory and disk, very fast recovery, less table-locking issues, speeds up recovery & storage. there is a management overhead with InnoDB that requires your system to be optimised to use it but great if you go that extra mile)

  • Data limitations: None
  • Index limitations: None
  • Transaction support: Yes (ACID compliant)
  • Locking level: Row