MySQL vs MSSQL

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?
al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
0
John TsioumprisSoftware & Systems EngineerCommented:
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.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
Kent OlsenDBACommented:
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.
0
Scott PletcherSenior DBACommented:
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.
"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.