Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Oct 8, 2003

The comparison of SQL Server 2000 with MySQL v4.1

By Alexander Chigrik

Introduction

Often people in newsgroups ask for a comparison of Microsoft SQL Server and MySQL. In this article, I compare SQL Server 2000 with MySQL version 4.1 regarding price, performance, platforms supported, SQL dialects and product limitations.

Platform comparison

SQL Server 2000 only works on Windows-based platforms, including Windows 9x,Windows NT, Windows 2000 and Windows CE. In comparison with SQL Server 2000, MySQL version 4.1 supports all known platforms, including Windows-based platforms, AIX-based systems, HP-UX systems, Linux Intel, Sun Solaris and so on.

Hardware requirements

To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware:

Hardware

Requirements

Processor

Pentium 166 MHz or higher

Memory

32 MB RAM (minimum for Desktop Engine),
64 MB RAM (minimum for all other editions),
128 MB RAM or more recommended

Hard disk space

270 MB (full installation),
250 MB (typical),
95 MB (minimum),
Desktop Engine: 44 MB
Analysis Services: 50 MB minimum and 130 MB typical
English Query: 80 MB

MySQL version 4.1 is not as powerful as SQL Server 2000 and uses less hardware resources. To install MySQL version 4.1, you should have near 32 Mb RAM and near 60 Mb of hard disk space. The general MySQL version 4.1 installation does not require additional CPU resources.

Software requirements

SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine and SQL Server CE (a compatible version for Windows CE) and requires the following software:

Operating System

Enterprise Edition

Standard Edition

Personal Edition

Developer Edition

Desktop Engine

SQL Server CE

Windows CE

No

No

No

No

No

Yes

Windows 9x

No

No

Yes

No

Yes

No

Windows NT 4.0 Workstation with Service Pack 5

No

No

Yes

Yes

Yes

No

Windows NT 4.0 Server with Service Pack 5

Yes

Yes

Yes

Yes

Yes

No

Windows NT 4.0 Server Enterprise Edition with Service Pack 5

Yes

Yes

Yes

Yes

Yes

No

Windows 2000 Professional

No

No

Yes

Yes

Yes

No

Windows 2000 Server

Yes

Yes

Yes

Yes

Yes

No

Windows 2000 Advanced Server

Yes

Yes

Yes

Yes

Yes

No

Windows 2000 DataCenter

Yes

Yes

Yes

Yes

Yes

No

Windows XP Professional

No

No

Yes

Yes

Yes

No

MySQL version 4.1 comes in two editions:

  • Standard
  • Max

The Standard edition is recommended for most users and contains general MySQL features. The Max edition includes additional features such as the Berkeley DB storage engine, OpenSSL support, user-defined functions (UDFs), and BIG_TABLE support. MySQL version 4.1 requires the following software:

Platform

Operating System Version

Windows-based

Windows 95/98/NT/2000/XP/2003

Sun Solaris

Solaris 8 (SPARC)

FreeBSD

FreeBSD 4.x (x86)

Mac OS X

Mac OS X v10.2

HP-UX

HP-UX 10.20 (RISC 1.0),
HP-UX 11.11 (PA-RISC 1.1 and 2.0),
HP-UX 11.11 (PA-RISC 2.0, 64-bit only)

AIX-Based

AIX 5.1 (RS6000),
AIX 4.3.2 (RS6000),
AIX 4.3.3 (RS6000)

QNX

QNX 6.2.1 (x86)

SGI Irix

SGI Irix 6.5

Dec OSF

Dec OSF 5.1 (Alpha)

Performance comparison

It is very difficult to make a performance comparison between SQL Server 2000 and MySQL version 4.1. The performance of your databases depends upon the experience of the database developers and database administrator rather than the database's provider. You can use both of these RDBMS to build stable and efficient systems. It is also possible to define typical transactions used in inventory control systems such as airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working with different hardware and software platforms.

TPC tests

The Transaction Processing Performance Council (TPC.Org) is an independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy. The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute. The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the TPC-C test (OLTP test).

At the moment this article was written, SQL Server 2000 held the second position in the TPC-C by performance results. See Top Ten TPC-C by Performance Version 5 Results.

At the moment this article was written, SQL Server 2000 held the topTPC-C by price/performance results. See Top Ten TPC-C by Price/Performance Version 5 Results

MySQL does not participate in TPC-C tests, they make their own bench marktests. These tests are not independent, but if you are interested, see this link:The MySQL Benchmark Suite

Price comparison

SQL Server 2000 is currently available under two licensing options:

  • Processor license
  • Server/per-seat client access license (CAL)

The processor license requires a single license for each CPU on the computer running SQL Server 2000 and includes unlimited client access. You can buy this license when you do not know the number of the clients, (for example, if your users will connect to SQL Server 2000 through the internet). This license usually is cheaper than Server/Per-Seat CAL when there are many users connected to SQL Server databases. The Server/per-seat client access license (CAL) requires a license for the server and the licenses for each client device. You can use this licensing option when the customers do not need access beyond the firewall and the number of clients is low (for example, 10-20 users for SQL Server 2000 Standard Edition or 30-40 users for SQL Server 2000 Enterprise Edition).

Licensing Options

SQL Server 2000 Standard Edition

SQL Server 2000 Enterprise Edition

Processor

$4,999 per processor

$19,999 per processor

Server/Per-Seat CAL

with 5 CALs - $1,489
with 10 CALs - $2,249

with 25 CALs - $11,099



The MySQL version 4.1 is Dual Licensed. Users can choose to use the MySQL software as an Open Source/Free Software product under the terms of theGNU General Public License or can purchase a standard commercial license from MySQL AB. See MySQL Support and Licensing

The MySQL v4.1 Server commercial license is per database server (single installed MySQL binary). The price comparisons below were based on theMySQL Licensing Prices from MySQL AB.

Number of licenses

Price per copy (EUR)

Price per copy (USD)

1 .. 9

440.00

495.00

10 .. 49

315.00

360.00

50 .. 99

255.00

290.00

100 .. 249

195.00

220.00

250 .. 499

155.00

175.00

500 +

ask for quote, sales@mysql.com

Features comparison

Both SQL Server 2000 and MySQL version 4.1 support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article, I want to make a brief comparison of the Transact-SQL with MySQL dialect and show some SQL Server 2000 and MySQL version 4.1 limitations.

T-SQL vs MySQL dialect

The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by MySQL version 4.1 is called MySQL dialect. Transact-SQL dialect is a more powerful language than MySQL dialect. This is the brief comparison of T-SQL and MySQL dialect:

Feature

T-SQL

MySQL dialect

Views

General Views,
Indexed Views,
Distributed Partitioned Views

Not Supported

Triggers

AFTER triggers,
INSTEAD OF triggers

Not Supported

Stored Procedures

T-SQL statements

Not Supported

User-defined functions

Scalar functions,
Inline table-valued functions,
Multistatement table-valued functions

C, C++ external libraries

Foreign Keys

Supported

Supported for only InnoDB tables

Cursors

Supported

Not Supported

Arrays

Not Supported

Supported

SQL Server 2000 and MySQL v4.1 limitations

The table below lists some SQL Server 2000 and MySQL version 4.1 limitations:

Feature

SQL Server 2000

MySQL v4.1

column name length

128

64

index name length

128

64

table name length

128

64

max indexes per table

250

32

index length

900

1024

max index column length

900

255

columns per index

16

16

max char() size

8000

1048543

max varchar() size

8000

1048543

max blob size

2147483647

1048543

max number of columns in GROUP BY

Limited only by number of bytes (8060)

64

max number of columns in ORDER BY

Limited only by number of bytes (8060)

64

tables per SELECT statement

256

31

max columns per table

1024

2599

max table row length

8036

65534

longest SQL statement

16777216

1048574

constant string size in SELECT

16777207

1048565

Conclusion

It is not true that SQL Server 2000 is better than MySQL version 4.1 or vice versa. Both products can be used to build stable and efficient systems and the stability and effectiveness of your applications and databases depends upon the experience of the database developers and database administrator rather than the database's provider. However, SQL Server 2000 has some advantages in comparison with MySQL version 4.1 and vice versa.

The SQL Server 2000 advantages:

  • SQL Server 2000 holds the top TPC-C performance and price/performance results.
  • SQL Server 2000 is generally accepted as easier to install, use and manage.
  • Transact-SQL is a more powerful language than MySQL dialect.

The MySQL version 4.1 advantages:

  • MySQL version 4.1 supports all known platforms, not only the Windows-based platforms.
  • MySQL version 4.1 requires less hardware resources.
  • You can use MySQL version 4.1 without any payment under the terms of the GNU General Public License.

This is from MySQL version 4.1 documentation:

MySQL Server was designed from the start to work with medium size databases (10-100 million rows, or about 100 MB per table) on small computer systems.

Literature

1. SQL Server 2000 Books Online
2. MySQL 4.1 Downloads
3. Features Available in MySQL 4.1
4. The MySQL Benchmark Suite
5. GNU General Public License
6. MySQL Support and Licensing
7. MySQL Licensing Prices
8. Top Ten TPC-C by Performance Version 5 Results
9. Top Ten TPC-C by Price/Performance Version 5 Results

» See All Articles by Columnist Alexander Chigrik



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date