MySQL vs MSSQL

al4629740
al4629740 used Ask the Experts™
on
So I have been using MSSQL 2012 for a while now and I like it.  I am developing an aspx webform which requires that I host the page and database.  I found a good hoster but the database they utilize is MYSQL.

First of all are there any significant performance differences between the full SQL and MYSQL.  Would it be simple to convert my database to MYSQL?  Are there any size limitations to MYSQL?  Any other notable differences?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
While they are very similar in capability (at least on a basic level), there are definite differences that can cause you problems or at least headaches.  For example, the syntax is different in some cases.  The classic example I like to give is:

MS SQL: SELECT TOP 10 * FROM MyTable
MySQL: SELECT * FROM MyTable LIMIT 10

As you can see, different.  And one will not work on the other.  90% is the same... but there are differences.

I would recommend you setup a local MySQL Database (it's free) and test your code against it.  Generally, when you develop something you should stick with the database platform you develop on.
John TsioumprisSoftware & Systems Engineer

Commented:
While back when i was making some tests MSSQL was much faster than MySQL ...and somewhere on the Net i found a mention that MySQL was at its best on same performance level as SQL 2000...But in databases there are more lot things than single query performance...like good database design,licensing cost,clustering...unless you put the same load and run your tests you want get a definite answer.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
I am going to chip in and say that if you are considering a database change, also look at Postgresql, as your host may support this too.
I've used both and like features of both.  They both have things that I wish were different than they are.

Out of the box, MySQL uses its own extensions to ANSI SQL that look a lot different than what you're used to.  Unless you're used to developing in MySQL you probably want to turn on ANSI mode.  The SQL will probably run slightly slower than the MySQL native SQL, but the conversion will be easier.  For simple queries the difference, if any, will be too small to be noticed.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
A) First of all are there any significant performance differences between the full SQL and MYSQL.
Yes.  MySQL is noticeably slower, particularly for large volumes of modifications.  If you have any truly significant db size, you might want to re-consider using MySQL at all.

B) Would it be simple to convert my database to MySQL?  
No, not at all.  MySQL does not support many features that MSSQL does, and there are major syntax differences.  This is not unusual: it would also be a massive change to convert to Oracle, for example (which MySQL is pretty close to internally).

C) Are there any size limitations to MYSQL?  
As I understand it, if you use the default InnoDb structure, no.  If you use ISAM (gack!), then I think 2GB per file(?I think?).

D) Any other notable differences?
Gazillion!  Here's just one beauty that can bite you if you use Windows to store MySQL files:
"
On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names.
"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial