Click here to Skip to main content
11,789,512 members (53,183 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: MySQL
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 at 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 at 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 at 17-Nov-12 14:00pm
Nice links :-D
Maciej Los at 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 at 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
0 OriginalGriff 954
1 Maciej Los 765
2 KrunalRohit 666
3 CPallini 606
4 Richard MacCutchan 420

Advertise | Privacy | Mobile
Web02 | 2.8.1509028.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2015
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