Today is a good day to code

SQL Server 2000 vs MySQL

Posted: December 31st, 1969 | Author: | Filed under: Microsoft, Uncategorized | Tags: | No Comments »

SQL Server 2000 vs MySQL

Picture of Irv Owens Web DeveloperOne of the funny things about comparing these two systems is that they are both medium sized DBMS systems. Neither is really suited to huge enterprise level solutions, although both companies will say that they are ready for them. I have not implemented any system on Oracle, Borland, or DB 2 so I can't truly speak for their performance on an official level. On an unofficial level, however I can say that I have noticed that they are significantly slower with lighter load. With heavy load, however they perform much better with Microsoft SQL Server 2000 and MySQL falling off quickly. This is evident simply when taking a look at hosting companies who provide either SQL Server 2000 or MySQL, in a local installation, these systems are blazing, returning results for queries often in under 100 ms. In production however, when using shared hosting most of my queries take anywhere from 250 ms to regularly hitting 2000 ms. Both of which put me outside the desired window. When using Oracle locally, performance hovers around 100 ms. When using an Oracle based system in production, performance still seems to hover around 100 ms. I have heard and witnessed to some degree that Sybase feels the fastest, which each subsequent iteration of a query moving faster. Sybase is probably the smartest DB as far as adapting to use, while Oracle seems to be the strongest.

Frequently however for most web applications, SQL Server 2000 or MySQL seem to be just fine. Between the two of them, after working with SQL Server for some time, I have come to really love the enterprise manager, with it's associated tools, the Query Analyzer and the Profiler, however when working with full-text indexes, MySQL wins hands down. As far as performance is concerned, from what I have seen MySQL is faster with a moderate load. SQL Server seems better with heavier loads, and appears to have better caching. As far as that goes, it has MySQL flat out beat. MySQL could do a better job of using the cache for similar queries, for example if I were to use SQL like this:

SELECT id, username, password, lastAccess
FROM testDB

It would return that dataset and hold the dataset in memory. But, it wouldn't be smart enough to realize that this query is just accessing a subset of the one above:

SELECT id, lastAccess
FROM testDB
WHERE id < 100

From what I have seen, SQL Server does a better job realizing that those two SQL snippets are utilizing the same recordset. MySQL would treat the last query as a completely new query and go to the disk to get the data. This may improve some by using MySQL's enterprise version, Max DB, or in version 5, but from what I have seen in the betas, it doesn't seem much better.

But with the ease of full-text searching in MySQL, I will almost always prefer it to SQL Server 2000 until Microsoft starts using a built-in full-text searching solution.

Look at this:

SELECT id, field1, MATCH (field1,field2) AGAINST
('text to be searched') AS score
FROM table WHERE MATCH (table1,table2) AGAINST
('text to be searched')

That is so simple and easy to understand. Creating the index is just as easy. Creating a full-text index using SQL Server is fairly complicated, primarily as I indicated above because you have to deal with an external application. The full-text searching rules built into MySQL such as the 50% rule as you can see in effect by visiting Owens Performance Blog Search makes querying an effort for the user, but much of that can be handled by decent business logic.

Ultimately it comes down to performance for price. When compared this way, MySQL wins, it is free. Still, MS SQL Server 2000 is a good value, given it's strong performance, robustness, and quality of tools. I will never hate using it for jobs. Still, if given my preference I would choose MySQL.