Age old debate: MS SQL vs. MySQL

We're starting a new site and I could use some opinions here. Part of the site is a standard community type site (blogs, forums, etc), and that part is already built with MySQL in a LAMP environnment. No problem there.

But the other part of our site which we're about to start building is a huge database of products. Probably millions of entries that will be updated regularly (daily, weekly) via feeds. Both parts of the site will be seamlessly integrated so that the community side can tie things (like say product reviews) to particular products. So both databases need to "talk" to one another.

We'll be doing a number of queries on the product database to help eliminate duplicates, spit out appropriate data based on a user's search criteria, etc. SO the question is: Do we go with MySQL or MS SQL for the product database?

I'm seeing good reasons for both sides. It's certainly much cheaper if we go with MySQL, plus there'll be no problem integrating it with our community database, and we wouldn't have to worry about server issues either. But can such a huge database work smoothly in MySQL? Can it scale up over time or will we get to a point where our traffic has grown so much, so many people are doing queries at once, that it just crashes the system and we'd be better off with MS SQL?

Speed is certainly an issue as we want almost instantaneous search results (1-2 seconds max) from a user standpoint.

Any thoughts out there? Are there specific functions SQL can handle that MySQL can't? Is there a limit to productivity based on size?

Thanks!

 

I would do a trial run with MySQL first. You will have fewer headaches by using hte same database. MySQL should handle million record tables without much problem. The size of the records will also have an effect on the perfomance. Where you will start to get into trouble is trying to do fancy queries with multiple functions, joins, and subqueries.

MS-SQL does have features that MySQL does not have. Some of the syntax is different, so if you were to run both you would need to be careful. And there is a free version (with a 4GB limit) called SQL Express.

I would try MySQL first and see if it works for you. Even if it is slow, it might not be a lot better with MS-SQL. It could be a hardware/memory limitation. Full blown SQL is expensive.

I have never used MySQL for a database that size, but I have heard it scales fairly well. We have a system that between all the tables probably has between 5 and 10 million records. We use MS-SQL, but even then we sometimes have issues when too many people are on the system (no crashes, just timeouts, errors and general slowness) and requesting too many records. That is more of a hardware issue than MS-SQL though. From what I understand (and I claim no expertise on MySQL), MySQL will act in a similar fashion.

 

Thanks for the advice! That's actually what we're going to do. We're trying it out in MySQL first and can always bump up to the Enterprise edition if we need to. If that's still not working down the road, we can always dump the data and try SQL.

 

I use MySQL but why is it that it's either MySQl or MS-SQL what about Postgresql. I don't like to pay for something that I can get open source. Seriously MS-SQL cost a lot of money. Anyways best wishes with your venture.

 

I'm a big fan of MySQL, but when your database and sessions get real big, SQL Server is much better than MySQL. Better yet, use Oracle it's got tons of features (Clustering, Partitioning, Row Level Locks, etc.)

 

can use Stored Procedures in MySql?

 

MySQL has stored procs from 5.0 on.

 

You may have your answer already but I'll reply because this is fairly fresh in my mind from work, and maybe it'll be useful.

We use MySQL for wikis, blogs & some other new stuff. Where we hesitate to use it is larger in-house systems where schema changes happen often, the reason being that nearly any schema change (like adding a new column) in mysql still causes a complete export & import of the table which, if it's large, causes heavy resource usage, requires twice the storage of that table, and of course locks the table. On mssql, oracle or postgres, adding a column is usually a half-second non-impact change, and this is the case for other things too like dropping a column, expanding a varchar column, dropping an index etc.

Another reason is backups. Unless you can back up a replicated mysql server or buy someone else's online backup tool, there is no such thing as a non-impact online full backup with Mysql. Especially if you want a consistent image and use bin-logs for point-in-time recovery. If a full backup failed for some reason on MSSQL, we often just restart it during the business day - that's how little impact it has. It's also very fast compared to mysql dumps or ibbackup (innodb's tool.)

Then there are some of those new features. Stored procedures can be great for security (avoiding direct object access, sql injection, etc) & performance, but mysql still doesn't do execution plan caching for them, so performance can actually decrease.

Another example: subquery's work now, but use it to provide a key value to your parent query, and mysql appears to forget to use indexes. That can be painful.

There are other little things that annoy a busy dba.
Say you have to change rights for an account that was granted too much access. Forget to run FLUSH PRIVILEGES afterwards? Then nothing has changed.
Or say your app inserts an international phone# into a too-short field. Forget to turn on "Traditional" sql support? Then mysql truncates the value and doesn't return an error.

If you need to do lots of concurrent reads & writes on a big table and can't use Innodb things might get difficult because the locking mechanism is very simple - writes just lock the whole table. And even with innodb, if your query doesn't reference a unique key it'll cause a table lock too.

You can always figure out how to partition things & instead have lots of small mysql db's or replicated db's to avoid some of these issues, if that's feasible with your app.

It may sound like I don't like mysql, but I do think it has it's place, and it's always getting better. fyi, though I havent used postgres a great deal yet, it appears to be much more mature to me than mysql. The gui tools are useful & stable too.

MSSQL's tools are among it's best features. Yeah it costs money, but we'll run 100+ (1-50GB) db's on a single instance because the tools make it possible for our small team. Being able to schedule full & transaction backups, reindexing & consistency checks along with dump file cleanup & email notification for 10 db's in less than 5 minutes makes you spoiled. With mysql we wrote shell and perl scripts with our own error checking & notification & scheduled via cron. We have templates but it is always more work. mssql's trace tools make pinpointing bad queries pretty easy too.

With very big (1 TB +) db's, db management will be much more involved on mssql or anything else. How you do most most of these things will then change anyway.
Cheers.

 

MySQL has it's strength and weakness like all databases. I use it more to do dynamic web sites.

 

My dad works at Cingular and they used MySQL to store information for phone numbers, names, etc. Well the database got too big and now they're converting to Orracle, I don't know if you would want to spend that much money on a database, but as far as my knowledge goes I would rather spend the money, for the most performance.

 

Yeah Oracle is pretty much the default when it comes to database in big business. For a small business try postgresql or fine tune your mysql.

 

All you have to know is that some of the biggest sites online use MySQL such as flickr, slashdot, craigslist, wikipedia and many others. If it can handle their stuff, chances are it can handle yours.

 

True, you can add google, yahoo, ebay, etc.

 

MySQL is definitely better than MS SQL.

Features which were missing in old versions of MySQL, like

- Views
- Triggers
- Stored Procedures

etc has been added in MySQL 5. Along with it, they have introduced a new concept of partitioning (http://dev.mysql.com/doc/refman/5.1/en/partitioning.html) which will make searching more fast.

And as stated above, many big web applications like Flickr and Wikipedia etc are using mysql.

So conclusion is, MySQL really rocks.

 

Having those features doesn't make it better than MSSQL (which has had those features, and others, much longer than MySQL). Looking at it as only a software question, MSSQL beats MySQL hands down. Of course, the reality is that there is more to the question. MSSQL is pretty expensive. I saw that MySQL is starting to charge, but I doubt that it comes close to the cost for MSSQL. MSSQLs pricing puts it out of range for most small site operators.

However, if you are having your sites hosted, there are many places that host MSSQL just as cheaply as MySQL. Of course, as said above, if you do template type sites, most of them are built with a MySQL backend. Why bother changing it?

 

Those were old days when MSSQL was superior than MySQL. MySQL is hell fast than MSSQL. I don't know what has happened to Microsoft. Making everything heavier day by day. And SQL server has some issues while using with VISTA.

 

I've been working with Oracle databases for 15 years and think it's an excellent product, as opposed to MSSQL, which is just slightly better than Access (to avoid flames, I'm only joking ................ not really ) BUT I think for MySQL is more than up to online applications.

 

One thing to keep in mind, when talking about million+ row tables, is your analytical reporting. You're not going to be able to run big, trend analysis queries against your transactional based MySQL database without hindering the performance of your web application.

I maintain that for transactional purposes, MySQL does just fine, even for big businesses. But when you get to the point where you are starting to have long running queries in order to answer your business intelligence questions, it's time to move your data over to a DBMS that handles analytical reporting a little better. Features such as materialized views, bitwise indexing, the ability to turn logging off, etc, combined with the added benefit of moving your reporting data away from the same physical disk space as your transactional data is stored will make a world of difference. Not to mention that MS SQL Server handles 64 bit machines and supports 4 and 8 way servers (MySQL only supports dual processors)

There's a reason why you've never seen a Data Warehouse built on MySQL. That's becuase data warehouse analysts know that mysql is built for faster transactions, and not for heavy aggregation or reporting.

 

I've worked with both MS Sql Server (Yukon i.e. 2005) and Oracle 10g quite a bit, and they both have their advantages.

I love the fact that SQL Server 2005 comes with bundles products like DTS (and don't even try to say Oracle Data Pump even comes close), Reporting and Analytical Services (as opposed to Oracle's Discoverer product, which costs an additional 100k)

That being said, Oracle's materialized views, the ability to turn on and off logging per table, and database instances are some arguments for that product.

I LOVE Oracle's inline views, but I'm not so keen on they way they almost force you to do everything in one query, or use a cursor as opposed to using #temp tables like you do in SQL Server.

Oracle's PL/SQL is a little more robust than SQL Server in terms of procedural logic, but I always write shell scripts and embed my SQL anyways, so I almost never need to use it.

Now that Oracle has come down to a realistic price, maybe their stock will start to come back up a little. ;-] but the bottom line is they are pretty evenly matched. Can't help but to feel a little sorry for Sybase and DB2.

 

I know MySQL handles millions of records.I have seen MySQL server which is handling millions of transaction without any problem.Go to it.