Click here to Skip to main content
Rate this: bad
good
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 6:45am
Comments
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?
--SA
Abhishek Pant at 16-Nov-12 13:04pm
   
yes, do you want to pull us all information required for this question
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
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
good
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: http://www.supportsages.com/blog/2010/08/mysql-storage-engines-an-overview-their-limitations-and-an-attempt-for-comparison/
For a full comparison, see the MySQL manual - it explains it all.
  Permalink  
Comments
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 Mika Wendelius 251
1 Peter Leow 250
2 CHill60 170
3 Abhinav S 155
4 Rob Philpott 130
0 Sergey Alexandrovich Kryukov 8,613
1 OriginalGriff 6,546
2 Peter Leow 3,897
3 Zoltán Zörgő 3,456
4 Richard MacCutchan 2,472


Advertise | Privacy | Mobile
Web01 | 2.8.150123.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