MySQL table types04 January 2009
Was messing around writing a website authentication system using PHP, and at some point I ended up looking into performance of various MySQL back-ends (bit of a diversion as my authentication code was using SQLite). Wrote the summery below so that I could happily forget the essential details until I really (if ever) need to know them, and am sticking it on-line as any technical write-up I don't put on-line ends up getting lost..
- MyISAM assumes that updates and deletes are relatively rare, and if they do occur they are fast (i.e. single-condition search using unique key). I'm guessing that deletes are particularly expensive as they leave holes in the database that necessitates disabling of concurrent execution of both selects and inserts. Seems its optimisations are aimed at avoiding locking the entire table if possible, and the back-end is a more suited for searching.
- InnoDB seems to be aimed at scenarios where table updates are frequent, and hence locking entire tables is a performance killer. It is also allegedly a lot more robust when it comes to data integrity, and unlike MyISAM has transaction support which is a major feature if table cross-reference a lot.
- BerkeleyDB: As far as I can tell BerkeleyDB is comparable to InnoDB, but MySQL v5.1 dropped support for it.
- Memory: Known as Heap in older MySQL versions. Only stored in memory, so is lost when MySQL shuts down. Intended for exceptionally fast reading/writing of session data.
- Merge: Concatenation of several identical MyISAM tables. Looks like its intended to allow large datasets to be split into separate tables, possibly to reduce the size of on-disk lookup metadata.
- Archive: Only supports insert and select, and database rows are zlib-zipped as they are inserted.
- CSV: The data is stored in CSV format, which is human-readable. I'm guessing that this back-end is not optimised for heavy usage.
(4th January 2009)