Database engines in MySQL

Question

0.00/5 (No votes)

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

Vasudevan Deepak Kumar

Add a Solution

Comments

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?
--SA

Abhishek Pant 16-Nov-12 13:04pm

yes, do you want to pull us all information required for this question

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2012-11-16T11:04:00

Solution 1

Start here: http://www.mysql.com/why-mysql/white-papers/mysql_wp_mssql2mysql.php[^]. Registration is needed.

Other references:
http://stackoverflow.com/questions/129/how-to-export-data-from-sql-server-2005-to-mysql[^]
http://dba.stackexchange.com/questions/3153/need-to-migrate-sql-server-to-mysql[^]

Posted 16-Nov-12 11:04am

Maciej Los

Comments

Espen Harlinn 17-Nov-12 14:00pm

Nice links :-D

Maciej Los 18-Nov-12 17:37pm

Thank you, Espen ;)

msmcleod · Answer 2 · 2012-11-16T16:02:00

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.