Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 MySQL not as good as MS SQL?

Author  Topic 

Frost
Starting Member

33 Posts

Posted - 2005-04-23 : 11:44:17
I was reading from MS website, and I know it may be partial, so I am asking in here for impartiality opinion. They stated: ..."MySQL is an open-source database management system (DBMS). It uses client/server architecture and is a muti-threaded, multi-user database server. MySQL was designed for speed; therefore, it does not provide many of the features provided by relational database systems, such as sub-queries, foreign keys, referential intergrity, stored procedures, triggers, and views. In addition, it contains a locking mechanism that is not adequate for tables containing many write actions occurring simultaneously from different users. It is also lacking in reference to support for software applications and tools." (ref: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx )

Now, what I'm gather from what Microsoft is saying is that MySQL is lacking in features for the sake of speed. It is not a relational built databse setup, and cannot do as much as the MS SQL 2000 desktop server can. Is this a correct assumtion or are they being biased?

Cheers!
Vern
vernon at direcway.com


Former Nebraskan, fix it with bailin' wire or please pass the welder!

Vern

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-04-23 : 11:54:52
http://weblogs.sqlteam.com/robv/archive/2003/11/19/576.aspx


Damian
Ita erat quando hic adveni.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-04-23 : 13:42:08
I would say MySQL is good for basic basic sites that need database development. There are a LOT of small businesses who use it as their own backend. But as you have read it is not as robust, scalable, expandable, and near as functional as SQL Server. I would say stick with SQL Server if you can afford it and for large projects.

If you are building basic things than mySQL is a cheap alternative.



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-04-23 : 14:06:31
"not as robust"

Over the last few years I have completely failed to understand why organisations would bet the farm on a database that had this shortcoming.

The fact that it is "free" or "cheap" should not come into the equation on a commercial project. Briefs we receive often state "MySQL prefered" and the client is going to make $nnn,000 p.a. or more from the project - I just don't get it.

[EDIT] Forgot to include this link. I've no idea if the info. is current, or not, but the fact that it was correct at some time I find scary. An Isoteric database I definately don't need!

http://sql-info.de/mysql/gotchas.html

Kristen
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-23 : 15:15:32
Hi Kristen,
Your shortcut is indeed very informative: http://sql-info.de/mysql/gotchas.html It is listed in the above thread of Merkin, although Merkin's link is blog that lists the link you provided. Anyway, the author of the German "gotchas.html" site is using PostgreSQL version 8.0.1 so I went there and downloaded a copy *S*

I really found the link about the gotchas amusing. You would think THEY would read that and try to update the manner in which they build their software. Kind of like buying a Ford (or other) 1 ton truck and only having axles and tire under it that come with standard sized cars. May be big, and have great motor, but DON'T fill it with 2000 lbs of stuff in the tuck bed or you'll not be going too far. *smiling*

I have downloaded the MS SQL Server2000 desktop engine and have it running as a service. It is free to download, so for my interest in small databases, what is the need to buy a Microsoft (or other) database program? I do have Access 2000 and all I was looking for was a database managment program, other than Access, that could be used to build databases and conduct queries etc etc.

Perhaps Access is just fine... A GUI MS SQL program would be a nice breath of fresh air.
Vern




Former Nebraskan, fix it with bailin' wire or please pass the welder!

Vern
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-04-23 : 16:00:35
to say that mysql isn't scalable is ridiculous. Look at livejournal for example. look at slashdot. Both of these sites take tons of hits per day, livejournal takes 1000s of hits per second at it's busiest. Neither one of These sites would even exist if they had to use SQL Server. Not because of missing capability or lack of scalability, the cost of the license would kill them. Livejournal has (currently) 18 servers hosting their databases. Figure between 36 and 48 total CPUs to license that would run you between 200-300k for std edition licensing, or 800-1.2 million for ent. edition licensing.

I agree that mysql doesn't have some important features that SQL Server has. I also agree that I would not run my company ERP on mysql. but then again, I would think twice about running a big ERP on SQL Server as well.

Anyway, mysql is an improving product. They are adding features much quicker than Microsoft is with SQL Server (how many years since the last release?). Learning something new is never a bad thing.



-ec
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-23 : 16:45:46
Now, Please don't think of me as trying to be funny, but I would like the answer to this one. I've downloaded MS SQL Server Desktop Engine. It runs as a service on my Windows XP computer, and shows up in the system tray. IS there a regular MS SQL Server 2000 that is 'like' the MySQL and 'similar to' prostgreSQL 8.0.x versions that cost money? I thought that they could be downloaded free. I'm not a multi corporation, I'm one computer, with Apache sever and PHP also on my comptuer and just want to learn a bit more about databases and their use and relationships. I do have Access 2000 through my Office XP program and it is nice in that it has a regular program that I can use to create databases from already exisiting templates and/or creaet one from a blank template. I understood that Access is not a true relational database and not as sturdy as the the SQL group. I want to learn the newest technologies and I hate to spend time learing about XXX when YYY is the upcomming program. Make sense? Why buy a book to overhaul a carburetor and rebuild it, when you really should be getting a manual on adjusting and overhauling Electronic Fuel Injection systems.. just as an example.

you guys seem like a great bunch!!!

Former Nebraskan, fix it with bailin' wire or please pass the welder!

Vern
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-23 : 17:33:19
MSDE is a lighter version of the same engine that drives SQL Server. It sits in a position between MS Access and SQL Server. It can handle a larger set of data than Access can and is completely free to use and distribute. It cannot handle enterprise-level database applications though, it is only meant to handle small groups or single user applications, or light-to-moderate-traffic web sites. MSDE's archictecture is better suited to client-server and distributed applications than Access is.

BTW, SQL Server Express edition is the new "version" of MSDE, it replaces it and is based upon the engine used for SQL Server 2005. You can download the most recent beta version here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=97383299-E74B-4AF8-90C7-FA0CA20A10C4&displaylang=en

It will be released to production later this year, as will the full version of SQL Server 2005.
quote:
I agree that mysql doesn't have some important features that SQL Server has...Anyway, mysql is an improving product.
I agree about the improvement part, but to be fair, it is LONNNNNNNNNNNNNNNNNNG overdue. Version 5.0 is finally adding things that make it even possible to consider mySQL for significant applications. The fact that /. and other web sites use it may say a lot, but the fact that almost no large corporations (and zero financial institutions) use it says a great deal more.

My biggest problem with mySQL is not so much the product as the attitude of the development team. mySQL is for, and designed by, people who care only for speed, and not for their data. The "gotchas" link, while entertaining and informative, does not even come close to demonstrating this attitude as well as the mySQL bug reporting pages do. This link in particular is probably the best (but not only) example:

http://bugs.mysql.com/bug.php?id=5670

Forget the actual, unbelieveably huge bug for a moment and notice the first reply to the problem. It's like, "oh well, you were using a foreign key and it hosed your table. you should have known better." This encapsulates the essence of the mySQL development team: they just DON'T GET IT when it comes to data integrity. They literally cannot be bothered with it, especially if it would sacrifice performance. That's been their history and culture, and it's their own damn fault if they get criticized (rightly) for it. Even MS Access has stronger data integrity features, and I'll bet if you shut them off like mySQL does, it would perform just as well.

I'm not saying performance is not important, but would you prefer to use a bank with the fastest web site, or one that can't accidentally lose your entire life's savings?
quote:
They are adding features much quicker than Microsoft is with SQL Server (how many years since the last release?)
I love the irony of this, as nearly all open-source advocates bitch about Microsoft adding features that compromise security. Now that they're taking time to make sure SQL Server 2005 is solid and secure...

Sorry about the rant.
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-04-23 : 18:13:57
Vern,

MS SQL Server Desktop Engine (MSDE) is the free version of MS SQL Server 2000. It has capacity limitations but is essentially identical to the "priced" version.

IMHO, PostgreSQL over MySQL by a country mile... MySQL 5 "may" change that but the odds are long....

Poor old MS Access...it gets such a bad wrap with the sole complaint being scalability.
The funny thing is, when it comes to features, SQL Server (and other DBMS) has been trying to catch up to MS Access. View updateability, cascading RI, "external" language intergration to enhance domain structure/operations, composite key support.. Not a bad litte DBMS to get your teeth stuck into since you already have it.

Instead of buying a book on EFI systems, how about a book on why we have them in the first place.
A good understanding of what a relational database means, will give you the ability to adapt to any latest fad that comes out.

There are some great people on this site, welcome aboard.

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-23 : 18:20:30
Thanks robvolk!!

I downloaded the MS SQL Server Express and all the accompanying docs, htm, and template installers.

I'm running MS SQL Server Service Manager, and wondered if I need to "STOP" the service before installing the Server Express, or do I need to uninstall it completely?

Also it might also be a good idea then to uninstall the MySQL desktop server and related programs before, just to make sure nothing conflicts.

I installed the MySQL as part of a learning curve about using PHP. This book I have is a learning book on the use of PHP and one of the 3 elements was to use MySQL. I have Apache 2.0.53 running as a service on my coputer as well as MySQL. In the Apache folder in the htdocs folder I have a file called mysqltest.php and it has the following code:

html>
<head> <title>MySQL Connection Test</title> </head>
<body>
<h2>

<?php
$connection=mysql_connect ("localhost","root","")
or die("Sorry - unable to connect to MySQL");
echo ("Congratulations Vernon - you connected to MySQL");

?>

</h2>
</body> </html>

I put the "vernon" in there just to make sure it was actually working and not some superfulious script
If I uninstall MySQL can I change the script to point to the MS SQL Server Express database and work as well? Right now when I open up a browswer and type in http://localhost/mysqltest.php I get the message Congratulations Vernon - you connected to MySQL Should not Apache be able to also connect to the newer MS SQL Server Express?. kind of wondering about that.


Former Nebraskan, fix it with bailin' wire or please pass the welder!

Vern
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-04-23 : 21:21:16
You are using the php MySQL library to connect to MySQL, they will never connect to MSDE or SQL Express, they just aren't designed that way. You'd have to look around for a php library for SQL Express, I'm not sure if such a thing exists.

Damian
Ita erat quando hic adveni.
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-23 : 23:11:41
quote:
Originally posted by Merkin

You are using the php MySQL library to connect to MySQL, they will never connect to MSDE or SQL Express, they just aren't designed that way. You'd have to look around for a php library for SQL Express, I'm not sure if such a thing exists.

Damian
Ita erat quando hic adveni.



In my folder C:\PHP I have a file called ntwdblib.dll, and it is also found in the C:\Windows\System, and C:\Windows\System32 folders. I also have in C:\PHP\ext a file (among several *.dll) called php_mssql.dll This is in an older version of 4.0 PHP I have. I just downloaded the new PHP ver 5.0.4 but have not installed it as yet. If the php_mssql.dll was copied to the 2 system folders and registered, would that work? Or, would I need to find a .dll file that says php_mssqllib.dll somewhere out there?


Vernon

"Never argue with an idiot..they will only bring you down to their level and beat you with years of experience"

:)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-04-24 : 02:55:08
"IMHO, PostgreSQL over MySQL by a country mile"

Why didn't PostgreSQL get all the hype that MySQL did? I know very little abouter either, but PostgreSQL seems to have pretty much all that I would need, whereas MySQL ...

Kristen
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-04-24 : 06:12:13
PHP does have an MSSQL library, see the full reference here: http://www.php.net/manual/en/ref.mssql.php

Their MSSQL Server library seems to have enough functions to get you going, but I get the impression that it is not updated often enough and it does not support some of the advanced functions in ADO. So I would recommend you follow an alternative approach: you can use ADO as your provider by instantiating ADO objects through the COM function available in PHP. Here's an example:

<?
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER=localhost;UID=sa;PWD=; DATABASE=mydb";
$db->Open($dsn);
$rs = $db->Execute("SELECT * FROM table");

while (!$rs->EOF)
{
echo $rs->Fields['column']->Value."<BR>";
$rs->MoveNext();
}
?>


I haven't used PHP enough in commercial applications to advise you on the performance implications of this method, but I think most developers are doing okay with this method.

OS
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-24 : 07:54:19
quote:
Why didn't PostgreSQL get all the hype that MySQL did?
Probably because those pesky data integrity features (primary keys, foreign keys, constraints) got in the way. Programmers would actually have to learn something more than SELECT/INSERT/UPDATE/DELETE to use Postgres.

Postgres is definitely a prime-time database, the only real problem with it is (lack of) support and technical resources, and this is where mySQL has the edge over it.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-04-24 : 13:05:53
quote:
Originally posted by Kristen

"IMHO, PostgreSQL over MySQL by a country mile"

Why didn't PostgreSQL get all the hype that MySQL did? I know very little abouter either, but PostgreSQL seems to have pretty much all that I would need, whereas MySQL ...



PostgreSQL was much more difficult to install. That isn't the case so much anymore. Also, how the hell do you pronounce it?



-ec
Go to Top of Page

boxmonkey
Starting Member

23 Posts

Posted - 2005-04-25 : 20:57:43
post gress is how I've heard it pronounced.
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-25 : 21:02:45
I found a little tidbit about MS SQL in the php manuals online page in help. Just thought I'd pass it along to any that happen to browse in here and wonder about it. PHP 5 has changed quite a bit and there are many things already enabled.

What is said at the website: Chapter 67 at this link: http://www.php.net/manual/en/faq.databases.php#faq.databases.mssql

is...." 1. I heard it's possible to access Microsoft SQL Server from PHP. How?

On Windows machines, you can simply use the included ODBC support and the correct ODBC driver.

On Unix machines, you can use the Sybase-CT driver to access Microsoft SQL Servers because they are (at least mostly) protocol-compatible. Sybase has made a free version of the necessary libraries for Linux systems. For other Unix operating systems, you need to contact Sybase for the correct libraries. Also see the answer to the next question.

2. Can I access Microsoft Access databases?

Yes. You already have all the tools you need if you are running entirely under Windows 9x/Me, or NT/2000, where you can use ODBC and Microsoft's ODBC drivers for Microsoft Access databases.



So, it appears that the PHP5 has sort of "branched" out and accepted that not everyone will be using just MySQL, but may also be using Access, MS SQL Server, PostgreSQL, and others. Should rename it "PostSQL" pronounced (post squeal) or PreSQLY for Elvis Presquly..


Vernon

"Never argue with an idiot..they will only bring you down to their level and beat you with years of experience"

:)
Go to Top of Page

Frost
Starting Member

33 Posts

Posted - 2005-04-25 : 23:03:49
Just a quick note.... I bought a book at B&N called "Microsoft SQL Server 2000 for Dummies" (my kind of book..lol), and it has a CD in the back of Microsoft SQL Server 2000 120 day Enterprise Evaluation Edition. I check out the CD and installed the SQL Server Enterprise Manager.. NOW that's a pice of work, huh?? Pretty nifty! Question is, if I want to install the evaluation copy of the SQL Server, at the end of 120 days, can I buy just the license to continue to use that copy? It's not on a server, it's not on a business machine. It would simply reside on my home computer so I could learn a little bit more.

*smiles*


Vernon

"Never argue with an idiot..they will only bring you down to their level and beat you with years of experience"

:)
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-26 : 07:39:30
A fully licensed copy of SQL Server Developer Edition is $49:

http://www.microsoft.com/sql/howtobuy/development.asp

It is the same as the Enterprise Edition, except that you are not allowed to use it in a production environment.
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-26 : 08:22:47
quote:
Originally posted by Frost


On Unix machines, you can use the Sybase-CT driver to access Microsoft SQL Servers because they are (at least mostly) protocol-compatible. Sybase has made a free version of the necessary libraries for Linux systems. For other Unix operating systems, you need to contact Sybase for the correct libraries. Also see the answer to the next question.



You *can* do that, but I found that it's incredibly difficult to achieve. Far more difficult than, say, linking PHP into MySql, which is probably why MySql has a following on online-based operations such as /. and such - it's far easier to link to it from PHP. My guess would also be that a lot of the sites with mysql have started out small, and grown up.

The DBAs and developers on those systems are probably crying out for the sort of features that have been in SQL Server for years, and are only just coming in to MySql development in 4.x and 5 releases. Things that are fairly key, like stored procedures, foreign key relationships, even sub queries, let alone actually having a fairly wide-ranging GUI at the front of it all for those that prefer using that occasionally instead of having to fiddle about with command lines.

How you could trust your enterprise to such a product, when even basic things like Transactions and point-in-time recovery are unreliable or not present I don't know.

-------
Moo. :)
Go to Top of Page
    Next Page

- Advertisement -