Click here to Skip to main content
13,199,571 members (62,234 online)
Rate this:
Please Sign up or sign in to vote.
See more:
MySQL seems to be shipping with a lot of user-selectable database engines like InnoDB, MyISAM etc. For a starter to MySQL, these seems to be overwhelming. Which one is a good to start for the one migrating from SQL Server to MySQL? What is the basis / best practices for selecting a storage engine in MySQL?
Posted 16-Nov-12 5:45am
Sergey Alexandrovich Kryukov 16-Nov-12 12:05pm
I think the question does not make a whole lot of sense unless you also specify your platform and preferred language(s)... Why won't you tag them?
Abhishek Pant 16-Nov-12 13:04pm
yes, do you want to pull us all information required for this question
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Espen Harlinn 17-Nov-12 14:00pm
Nice links :-D
Maciej Los 18-Nov-12 17:37pm
Thank you, Espen ;)
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Each storage engine has its own pro's and con's, so choosing which storage engine to use largely depends on your application needs.

It's also worth noting that the storage engine can be chosen per table, so you can have different tables using different storage engines in the same database. The system wide storage engine setting just tells MySQL what storage engine to use for a table if you don't specify one when creating it.

The main differences are as follows:

InnoDB supports transactions (i.e. you can make changes then rollback on error), MyISAM doesn't.

InnoDB can enforce foreign key constraints, whereas MyISAM allows you to define them but doesn't enforce them.

MyISAM allows full-text indexing on TEXT fields (i.e. text blobs), whereas InnoDB doesn't - you also can't set default values on TEXT fields within InnoDB.

For most applications, I'd recommend InnoDB as it most closely matches what you'd expect coming from Sql Server.

However, if speed is your main concern, then MyISAM is worth considering. MyISAM is VERY fast, as it doesn't need to check foreign keys or worry about transactions. The full-text indexing on TEXT fields is also a big bonus.

Other storage engines such as ARCHIVE are more concerned with space. ARCHIVE tables are compressed so they're small, but you can't delete from them or index them. This may sound like they're pretty useless but they're ideal for audit trails.

There's an article here on comparisons:
For a full comparison, see the MySQL manual - it explains it all.
Espen Harlinn 17-Nov-12 14:01pm
Good reply :-D

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171020.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100