- Database: A database is a collection of related information stored in a structured format.
- Relational Database: A collection of "related" databases, for example and travel agent booking system consisting of customer, airline and account details, can be combined and would be referred to as a Relational Database.
- Record: A database comprises of records and each record contains all the information about a single member or item.
- Field: A record comprises of fields where each field is a single piece of information relating to the record for example your Medibank record may contain individual fields for first name, last name, Medibank number, date of birth, etc. All records have the same structure and so contain the same fields but obviously each field has its own unique data.
- DBMS: Database Management System.
- RDMS: Relational Database Management System.
- Constraint: A constraint is a property assigned to a column or the set of columns in a table that prevents inconsistent data values from being placed in the column/s. They are used to enforce data integrity and therefore database reliability. There are several types of data integrity including Entity, Domain, Referential and User-Defined.
- Dirty read: If a database allows a user to read updated data that has not been "committed" by the user inputting the data then this is called a dirty read. A database should only allow data to be read that has been committed.
- Query optimiser: When you perform a report or query the optimiser is responsible for determining the fastest and most efficient way to perform the operation. Often the query optimiser relies on table and index statistics to determine the best way to optimise the operation.
- Primary key: The Primary key is a field that uniquely identifies each record in the table and is often an item that is unique for each record in the database such as you drivers license number for example. The database can generate a primary key itself if you require it but it makes more sense to use an existing unique field. A foreign key identifies records in an external table.
- JDBC: JDBC is an API that is included in J2SE and J2EE releases that provides cross-database connectivity to a broad range of SQL databases and even provides access to other data sources, such as spreadsheets or flat files.
- Stored procedure: SQL commands are compiled into a stored procedure that can be called and executed repeatedly saving time and boosting performance.
- Triggers: A trigger is an action that automatically takes place when a certain database event occurs, the action could for example be a stored procedure that "fires" when a trigger condition is satisfied.
- OLAP: OLAP is an abbreviation for Online Analytical Processing and is a category of tools that provides analysis of database data. This enables users to analyse different dimensions of multidimensional data and can for example provide trend analysis.
- ODBC: ODBC is an abbreviation for Open DataBase Connectivity, this is a standardised method for accessing databases developed in 1992 by the SQL Access Group. ODBC makes it possible to access and exchange data between any compliant database systems.
- ACID Transaction Management: ACID is the cornerstone for DBMS and is an abbreviation for Atomicity, Consistency, Isolation, and Durability. Obviously if you are attempting to purchase a DVD online at the same time another user is attempting to purchase the identical DVD you would want to ensure your transactions were independent and distinguishable with no overlap to ensure a reliable and accurate transaction occurs.
- Entity integrity: Refers to the fact that there are no duplicate rows in a table.
- Domain integrity: Ensures valid entries in a column by restricting the type, range or format of the data values possible.
- Referential integrity: Where row data is shared between more than one record this ensures that the data cannot be deleted while there are still dependent records.
- User-defined integrity: You may have your own business rules that do not fall into the other data integrity categories, these can be enforced by "User-Defined Integrity"
Ensuring the database performs well is not as simple as it sounds, and can appear a black art to the uninitiated. Unless you know what you are doing, a self install is probably a bad idea. Based on your own scenario, most vendors will be able to suggest settings that will improve the performance of the database appreciably over its standard out-of-the-box configuration. We did find, however, that some of the products include Wizards that did a good job of optimising the database by asking the user to select simple descriptions of their database type and usage patterns.
Security will always be a very important consideration given that the database will contain sensitive customer information such as credit card details. Not only is it important that the database be encrypted, it is also important to provide an audit trail to ensure there is no abuse of privileges by "authorised staff".
The front end for the database in this scenario would include data consistency and bounds checking but the database should also support robust integrity checking of its own.
What follows is not a detailed breakdown of the databases tested but rather a highlight of some of the more unique features of each vendor's product.
1
Blah - 23/12/05
WHERE THE HELL IS POSTGRESQL
» Report offensive content
2
anon anon - 23/12/05
You forgot to add PostgreSQL to the batch which is quite a bit better then MySQL. It compares quite nicly to the commercial DB's
» Report offensive content
3
x - 23/12/05
First off, you've really not done justice to the fact that MySQL's feature set, and very correctness in the sense of the rules of relational theory, are far beneath the other players.
Secondly, where is PostgreSQL in these tests. *That* would make for a much better feature-for-feature performance test with the enterprise-cl**** commercial solutions, and would probably fare very well.
» Report offensive content
4
Agent Smith1024 - 23/12/05
You have no clue what you are talking about! MySql is a toy compared to the others. I'm pro open source but please let's be realistic.
» Report offensive content
5
Michael Petch - 23/12/05
I'm curious about the validity of this statement:
"SQL Server Express is one of two free databases we tested and is actually Microsoft's replacement for its earlier free offering the Microsoft Desktop Engine (MSDE) which was based on the old Access technology."
As far as I am aware MSDE has always been based on MS-SQL (Not access), and that it was a throttled back version there of.
» Report offensive content
6
Tony Dixon - 23/12/05
Really not a fair comparison for the Microsoft Product. MS SQL Server 2005 Standard Edition is the equivalent product to the IBM and Oracle offerings and should have been the one on test.
» Report offensive content
7
Lamar Owen - 23/12/05
Please review PostgreSQL as well. The Windows installer comes with a GUI admin tool. www.postgresql.org
» Report offensive content
8
Robert McKee - 23/12/05
ROFL, did AB pay you or are you really that blind?
» Report offensive content
9
C Smith - 23/12/05
Are we to guess that a cobol database would have required too much coding for you to test it also?
» Report offensive content
10
Craig Ringer - 23/12/05
If you think MySQL is good, check out PostgreSQL.
Free ($0, open source), with a more "enterprise" feature set, better SQL support, and with a robustness that MySQL is only now beginning to approach. The documentation is fantastic, and it runs on Windows and UNIX.
Downsides include lack of GUI consoles (as far as I'm aware - I've never cared) and no paid support from the developers, though it's re-sold under several names with paid support.
» Report offensive content
11
Kenneth - 23/12/05
I dont find it serious to rate DBMSs without benchmarks? Im sure that MySql has no business being in the field if real benchmarking was done.
» Report offensive content
12
Alan Pater - 23/12/05
Hello? You missed the most important one! http://www.postgresql.org/
» Report offensive content
13
Phil Hewitt - 23/12/05
What about postgresql? There is more that one OS RDBMS.
» Report offensive content
14
Laluque Florian - 23/12/05
Why not speek about Postgresql ? It is free (BSD licence), it is known to be reliable too, and it has advanced feature of Mysql 5 for years...
» Report offensive content
15
Laurent Debacker - 23/12/05
A good comparison should also consider PostgreSQL since it has a lot of features MySQL hasn't, and has a much more friendly license that makes truly free as in beer.
» Report offensive content
16
Mz X - 23/12/05
M$SQL Express, DB2 Express... Oracle Enterprise? I think Oracle "One" the same category...
» Report offensive content
17
Ricardo Barcelona - 23/12/05
What about PostgreSQL 8.x?
» Report offensive content
18
Eivind Uggedal - 23/12/05
What about a look at PostgreSQL. It would be interesting to see how you would compare it to your Editor's Choice, MySQL.
» Report offensive content
19
Mike McMahon - 23/12/05
"replacement for its earlier free offering the Microsoft Desktop Engine (MSDE) which was based on the old Access technology"
NO, thats not true! MSDE was never based on Access!
Some might say MSDE was "based on SQL Server" and
even that is an understatement:
MSDE == SQL Server.
Literally, MSDE uses the exact same code as SQL Server. The only difference between MSDE and SQL Server 2000 was the throttling and lack of administrative tools. Engine-wise, MSDE is exact same as SQL Server 2000
» Report offensive content
20
shantanu - 23/12/05
I don’t agree to Editor. MySql Does not have XML Support. It is not enterprise enough, and does not support UDF.
I think it is comparatively slow. I think you can declare it winner of Express products (Oracle Express, MSSql Express, IBM DB Express) etc, however when it comes to enterprise computing/Solutions there is no comparison.
» Report offensive content
21
Hugh R. A. Nobb - 23/12/05
This is arguably the worst technical review I have ever read.
It is no more than a feature-by-feature "tick list". It has numerous technical factual errors, lack of justification of each product's relative merits and weaknesses, a ridiculously uninformed bias towards MySQL and it compares apples to oranges (Oracle 10g standard vs. SQL server Express 2005? Absurd!). It also falls prey to flagrant business buzzword BS.
I can only ****ume the author has never actually worked in the development of production databases. Probably a third-grade marketing consultant who feels qualified to comment on the basis that he's knocked up a homepage for his grandmother on the web.
Spreading trite and vacuous misinformation in the way this site has done here fills me with utter contempt for your futile site. I will not take builderau.com.au seriously in the future.
» Report offensive content
22
Developer - 24/12/05
The MSDE was based on previous versions of SQL Server not Mircrosoft Access; Access is an ISAM database, but you are probably better off just writing about Excel and not SQL Server.
» Report offensive content
23
Developer - 24/12/05
Relational Database: A collection of "related" databases, for example and travel agent booking system consisting of customer, airline and account details, can be combined and would be referred to as a Relational Database.
No.
Local Area Network: A Local network in your area; that's more accurate, but still silly - your definition is just wrong:
A collection of databases (they don't have to be related) is known as a catalog (SQL-92). You've confused table with database.
You need to do some reading on relational theory.
» Report offensive content
24
FACORAT Fabrice - 24/12/05
Whereas I do agree with you concerning MYSQL. MYSQL is not the most feature full OpenSource DB.
IMHO PostgreSQL provide more features and with version 8.x is able to run on Windows.
See http://www.postgresql.org/about/ and http://www.postgresql.org/about/advantages
PostgreSQl supports :
- subqueries
- view
- stored procedure in multiple language ( python, java, perl, c, Pl/PgSQL )
- Triggers
- several index type
- user defined functions
- foreign keys
- and so on
It's a really interesting database :)
» Report offensive content
25
Jonah H. Harris - 24/12/05
I find it odd that this editor only used MySQL to compare open source databases to commercial vendors. When comparing enterprise-cl**** databases such as Oracle, DB2, and SQL Server one would think the editor would similarly compare enterprise-cl**** open source software such as PostgreSQL, EnterpriseDB, and Firebird rather than MySQL. The first questions that come to mind are, is the editor unaware that other open source databases exist, or did MySQL sponsor this article?
It would be hard to argue that the editor doesn't know about such things as EnterpriseDB, who sells an uncrippled and enhanced version of PostgreSQL, has won several major awards, and has been getting much better press than MySQL in the last six months.
» Report offensive content
26
Mike - 24/12/05
I didn't see the "test" part. Just a list of 4 products and the editor's pick.
» Report offensive content
27
Minnie Bannister - 24/12/05
The term "relational database management system" has nothing to do with relationships between databases, or within databases. It derives from the mathematical concept of a "relation" whic is in essence a table, eith rows, columns and a heading. A RDBMS then is a database composed of tables (as opposed to e.g. trees or graphs, the basic forms of hierarchical and network DBMSs). Relations share many characteristics with sets, and many of the operations between sets have analogous operators between relations. Thus it is possible to peform operations on and between whole tables, instead of records (rows) or fields (row/column intersections). SQL supports many of these table level operations, however host languages like Java or C++ do not, so most programs are constrained to the limited functionality which is mapped through JDBC, ODBC, etc.
I found this article disappointing, having many inaccuracies and misleading statements. The author apperently has no fundamental knowledge of the field.
» Report offensive content
28
abc cde - 24/12/05
What about PostgreSQL?
» Report offensive content
29
UCLA - 24/12/05
What about security? How it's security managed in each engine?
» Report offensive content
30
jon - 24/12/05
"Relational Database: A collection of "related" databases, for example and travel agent booking system consisting of customer, airline and account details, can be combined and would be referred to as a Relational Database."
Thats misleading a best.
» Report offensive content
31
John Tobias - 24/12/05
Firebird >= MySQL
» Report offensive content
32
John Tobias - 24/12/05
Firebird >= MySQL
» Report offensive content
33
none - 24/12/05
Tested? Where are the tests? All you guys did was read the product documentation and summarize it. It's also quite obvious that you already had a winner picked in your mind before even starting the project. Not only that, but why use BETA designated products (unless you agree that MySQL is permenantly BETA or something ;))
Can you say "worthless"? I thought you could.
» Report offensive content
34
Jsck Tompson - 24/12/05
This was one of the most biased reviews I have ever read. The whole time reading you knew the conclusion that MySQL was going to the choice. None of the products were equal in their cl****. How can you compare MS SQL Express to MySQL in this scenario? MS SQL Express was never meant for this type of activity. Just because it is free doesn't make it a good reason. You should have test MS SQL Workgroup which is the next step up and it would have been cheaper then IBM's DB2 Express. Also, you tested beta software against non beta software. Next you test with version 4.x of MySQL and then say wait version 5 is released with all these new vendor stated features that must work and be even better. Never once did you state any results from tests run. You merely stated product feature lists. How about you redo your database test (or actually do them in the first place) with the latest product version from each vendor. Also remember to compare the products based on what role the vendor says they should be used for. In other words compare workgroup versions against other workgroup versions. Not your current cheapest offering by a vendor.
» Report offensive content
35
Matt England - 24/12/05
Why is PostgreSQL not mentioned in this article?
-Matt
» Report offensive content
36
Jim Whitten - 24/12/05
Good review -- thanks for the posting.
Just curious: Why wasn't PostgreSQL ( http://www.postgresql.org/ ) included in the review?
» Report offensive content
37
Jack Sprat - 24/12/05
You kinda missed the boat by not testing PostgresQL.
» Report offensive content
38
Matt Cramer - 24/12/05
Hi,
this may have been mentioned before, but the stated definition of "relational database" in the article is completely incorrect: a relational database is not simply a "collection of related databases". Rather, the term refers to the method in which ****ociated _data_ are organized within the database system.
The relational approach is to connect pieces of data with each other by using table structures, with each row representing a set of related data items.
There are also other types of database systems other than relational; however these are experimental and certainly all of those referred to in the article are of the relational type.
» Report offensive content
39
Jim Van Zandt - 24/12/05
Was PostgreSQL considered?
» Report offensive content
40
brian white - 24/12/05
This article SUCKS.
Why did you bother installing the software if you NEVER used it in your 'Road Test'. How is it a road test to compare the feature list?
On your first page you said you'd need to simulate load, etc. Well? Is the article missing some pages? Where do you do that?
See slashdot for a detailed discussion of how much this article sucked.
» Report offensive content
41
Euan Garden - 24/12/05
Lots of comments, first the factual errors;
1/ MSDE was not based on Access technology, MSDE 1.0 was based on SQL7, MSDE 2000 was based on SQL Server 2000
2/Single CPU/Scheduler issue, what you fail to point out is that Express is limited to one CPU, not core, hence on a HT enabled machine it will use both "processers" on a multicore machine it will use all cores. Hence a faster CPU is not the answer, a more modern one is.
3/ 4GB Database size limit, this is for a single database, you can have many databases on an instance and many (16) instances on a box.
4/ Upgrade Path, is not to Std Edition it is to workgroup edition. With correctly written apps express can scale to 100's of users as MSDE did before it.
5/ Express will include Reporting Services in an update next year, this was announced in June of this year, your info is out of date.
6/ Crippleware, I have no idea what you mean by referring to the No 1 OLAP Server(http://www.olapreport.com) as crippleware?
7/ 4GB Database Size, putting pictures inside the database is not always the right architectural solution, sometimes it is better to have them in the file system and maintain links in the db. It depends on the size of the images and the transaction characteristics so it may not impact the system growth at all.
8/ UNable to connect in Windows Auth mode comment, I suspect you did something wrong as this is the default and works, period.
9/ Snapshot isolation level is just one of several isolation levels supported by SQL Server Express.
Now the broader questions;
1/ Why are you using an 18month old version of SQL server express? Beta 2 was released in summer 05. There have been many CTPs since then.
2/ Why compare a free SKU of SQL Server with a low end paid SKU of DB2 and a full SKU of 10G? Why not use SQL Server Workgroup Edition as an alternative or in addition?
» Report offensive content
42
asd - 25/12/05
I find it strange that business software is compared with the not so strict database MySQL. You would really expect PostgreSQL to be in this list.
» Report offensive content
43
ANIL MAHADEV - 26/12/05
WHERE IS DB2 ???
Do u have any idea of what DB2 is capable of ??
I dont think you have tested anything.
DB2 was the first relational database to have the best darn support for latest technologies.
This study is not considered to be worth the time
Do more research and then come to a conclusion.
» Report offensive content
44
Ketema Harris - 28/12/05
I can't believe you did not review PostgreSQL. PostgreSQL is free, open source, and has had many of the features you lauded for MySQL long before MySQL. PostgreSQL lives up to its moniker of "Most advanced open source database" and I would put it up there as one the most advanced period. You should definitely go back and compare it.
» Report offensive content
45
anonymous - 29/12/05
For a database as described here, I dont blame a company for using a piddly free database. This example used sounds like a database I would design in my 12th grade computer cl****.
I am a DB2 DBA and support DB's up to 2 TB. Try running one of those (reliably) on MySQL. I know MySQL has gotten much better over the years and it probably makes sese to use in some enviroments, but not mine. No way.
» Report offensive content
46
Anonymous - 29/12/05
There sure are a lot of nay-sayers from people that say "I'm sure" this, or that, without actually using the product. One of our MySQL servers is cruising away handling 10+ million transactions a day, 400GB database, with about 95% idle CPU on a dual Xeon setup. While there are certainly limitations to what can be done with any database and I'm not putting any other database down by saying this, I'd hardly consider MySQL a 'toy', nor would Yahoo or many other large organizations that rely on it.
MySQL on 10.0.0.2 (4.1.8-standard) up 189+12:39:59 [11:42:29]
Queries: 1.9G qps: 123 Slow: 129.9k Se/In/Up/De(%): 61/06/09/01
qps now: 271 Slow qps: 0.0 Threads: 309 ( 1/ 9) 75/03/05/00
Cache Hits: 389.4M Hits/s: 24.9 Hits now: 33.7 Ratio: 33.3% Ratio now: 16.7%
Key Efficiency: 98.3% Bps in/out: 85.9/ 18.0 Now in/out: 19.4k/77.6k
» Report offensive content
47
Fred Snertz - 30/12/05
NO POSTGRESQL? Your credibility just sank to zero.
And you can't use that "but there's no Windows port" excuse anymore...
» Report offensive content
48
Anonymous - 30/12/05
See here: http://msmvps.com/blogs/greglow/archive/2005/12/30/79994.aspx
Do you know what you are talking about?
» Report offensive content
49
Richard David - 03/01/06
The fact that you've neglected to introduce Ingres and Postgres into your comparison says it all.
DB2, SQL Server and Oracle will end up costing you dear.
My company compared MySQl, Postgres and Ingres. Results:-
MySQl is a toy,
Postgres is mucho powerful but doesn't scale too well.
Ingres is absolutely outstanding. We still can't believe its free!
» Report offensive content
50
Dick Goulet - 06/01/06
Correction from MySql's site & License documents. It's free so long as you use it for non-commercial purposes. Use it for a commercial purpose as you suggest and it's no longer free. This is a common misconception that MySql AB's laywers are more than happy to correct when found.
» Report offensive content
51
G - 29/08/06
I can't remember when I last saw such a poor example of a "side by side comparison" of products before. To believe that MySQL is anything more than a toy is shocking, to miss out PostgreSQL entirely is criminal...
» Report offensive content
52
Alan Bourke - 31/08/06
Why is SQL Server 2005 Express Edition the worst? Because it's Microsoft? Or because you got a lot of facts wrong or were unable to do something as basic as get the authorisation right as a previous poster mentioned?
» Report offensive content
53
Ashir Palluka - 14/09/06
ie, yi, yi
free is not free, and cheap is not cheap. You mix and match and my head can not understand why you interchange semi-enterprise with non-enterprise with junk. Sure if I am making a website for home I use MySQL, but maybe it not work with big applications that use SMNP, enterprise managers, performance monitors, etc ... Oh yeah, i have the source code so I can just code all these tools myself ... no thankyou boss.
» Report offensive content
54
Ashir Palluka - 14/09/06
ie, yi, yi
free is not free, and cheap is not cheap. You mix and match and my head can not understand why you interchange semi-enterprise with non-enterprise with junk. Sure if I am making a website for home I use MySQL, but maybe it not work with big applications that use SMNP, enterprise managers, performance monitors, etc ... Oh yeah, i have the source code so I can just code all these tools myself ... no thankyou boss.
» Report offensive content
55
John Bergeron - 05/11/06
Why would you compare SQL Server Express and Oracle? Thats apples to oranges.
» Report offensive content
56
coada - 29/11/06
"For Web Sites, Enterprise IT, and Government IT:
* MySQL Network is the comprehensive subscription offering that provides database developers and DBAs with everything they need to successfully develop and deploy database solutions with MySQL. It includes Certified Software, updates and upgrades, proactive alerts and advisors, the online MySQL Knowledge Base, and full production-level technical support. The Certified Software (database server, connectors) is provided under the GPL License. Optionally, customers may choose a Commercial License. "
See the "The Certified Software (database server, connectors) is provided under the GPL License". This means, FREE TO USE. So, mr Dick Goulet you are wrong.
Anyway, even if I am a MySQL fan, I still find that the winner was chosen from the beginning. However, I believe that MySQL could win even if some actual benchmarking would have taken place. I'm still curious about Postgre.
For the guys that said MySQL is a toy (or in the end, any db server) and that product X has waaaaaaay more features and stuff: ever considered that some of those "features" are there just to accommodate your lazy behind or little knowledge? I'm amazed about how many programs out there use a s... load of stored procedures and co, just to pull out some simple set of data.
» Report offensive content
57
harveysburger - 02/02/07
angry mob of DBAs hehe
» Report offensive content
58
Me - 27/02/07
Has anyone mentioned that PostgreSQL isn't on this list?
» Report offensive content
59
From Holland - 10/03/07
It is possible to install more MS SQL Express databases on a system so you can have more than 4GB Database size and more than 1GB bufferpool.
I want to use a quad core processor so I can use more cores..but can a second quad core processor be used by the other MS Express database?
I'm not an expert so I hope someone can give me some feedback on my thoughts.
» Report offensive content
60
Kurt - 21/05/07
Wow! you sure got your **** chewed out. There sure are a lot of Postgres fans out there. I'll have to check that out before I get too commited to mysql.
with fans that rabbid it must be good
» Report offensive content
61
Baxter Basics - 08/06/07
Worst. Review. Ever.
I mean, totally pathetic. The author clearly has absolutely no idea what what relational database management systems are.
builderau.com has become a laughing stock of the technical community over this shiteshower of an article, and quite deservedly so.
» Report offensive content
62
Baxter Basics - 08/06/07
Worst. Review. Ever.
I mean, totally pathetic. The author clearly has absolutely no idea what what relational database management systems are.
builderau.com has become a laughing stock of the technical community over this shiteshower of an article, and quite deservedly so.
» Report offensive content
63
Ed Mansfield - 11/06/07
We have versions of our software that run on both Oracle and MySQL. Our experience has shown that MySQL wins hands down over Oracle in just about every category: ease-of-install, administration, standard SQL syntax support, flexibility of SQL syntax support and, most importantly, performance. Admittedly our application isn't a heavy-weight in terms of concurrent sessions or DB size, and Oracle10g is a lot better than 9i in performance, but MySQL runs rings around Oracle when installed "out of the box" without any performance tuning.
The 'mysqldump' utility is a godsend with no direct equivalent in Oracle, making it extremely easy to migrate databases from one site to another. With the dollars Oracle expects, having no ability to generate an SQL script from an existing database is bordering on abusive.
» Report offensive content
64
Amir Ali Tayyab - 09/08/07
Yes, I agree with the Editor's choice. Database is an integral part of any organization today and its security, global availability, affordability and continous development on part of MySQL can't be matched by any other database in market today in 2007.
» Report offensive content
65
Amir Ali Tayyab - 09/08/07
Yes, I agree with the Editor's choice. Database is an integral part of any organization today and its security, global availability, affordability and continous development on part of MySQL can't be matched by any other database in market today in 2007.
» Report offensive content
66
Amir Ali Tayyab - 09/08/07
Yes, I agree with the Editor's choice. Database is an integral part of any organization today and its security, global availability, affordability and continous development on part of MySQL can't be matched by any other database in market today in 2007.
» Report offensive content