A comparison of
MySQL And Oracle
Jeremy Haubrich
Importance of choosing the
right database
High cost for an enterprise system
Databases can hold the keys to the castle
Databases can give companies a competitive
advantage
Direct comparison isnt fair
Oracle and MySQL are built for different markets
Oracle is designed for the enterprise customer with a big
enough budget and more complex business needs
MySQL is a lower cost database that is most commonly
used for database driven web sites and non mission
critical applications
Features that look identical in a brochure may be
implemented very differently (ex. row level locking)
Cost
Oracle
$36,000 per processor for Enterprise Edition +
optional $8,000 for support and software updates
$5,000 per processor for Standard Edition + optional
$1,100 for support and software updates
MySQL
Free to download
$500 for a commercial license + optional
$1,500-$62,000 for different levels of support
Oracle Features
Oracle Management Server
Oracle Change Manager
Central administration, monitoring, and tuning of multiple databases
Schedule and share database tasks with other admins
Allows you to create a change plan and see the effects of your changes
before actually implementing them
Also shows any changes that cannot be applied
Administrative Alerts
Oracle will contact the admin by email or pager when an error
condition occurs
Can be scheduled to reduce false positives
More Oracle Features
Capacity Planning
Tracks usage patterns to help admins plan for upgrades
Feedback on CPU, disk and query performance
Query Optimizer
Oracle chooses the most efficient way to execute a SQL
statement
Analyzes tables and indexes to minimize the cost of the
transaction
MySQL Features
Easy to get started with
Many free GUI management tools like
PHPMyAdmin
Speed is emphasized over lesser used features
Uses very little overhead
MySQL uses just under 1MB of RAM on my laptop
My Oracle 9i installation uses 128MB while idle
More MySQL Features
Beginning to support advanced features
Stored Procedures, Triggers, Views in version 5
Select statements with sub-queries were introduced
in version 4
Transactional tables
Cascading updates and deletes
Available through the third-party InnoDB storage
engine
Migragtion from MySQL to Oracle
As your company grows Oracle may be a better fit
Migration tools are available (Oracle Migration
Kit)
Companies may use both Oracle and MySQL
Differences in syntax
http://troels.arvin.dk/db/rdbms/
Not all databases use the same SQL syntax
Example: Returning the first 5 rows of a query
MySQL
Select columns
FROM tablename
ORDER BY key ASC
LIMIT 5;
Oracle
Select * FROM (
SELECT columns
FROM tablename
ORDER BY key ASC
) WHERE ROWNUM <= 5;
Timestamps
MySQL
Create a field of type TIMESTAMP and enter an invalid date.
February 29 2003.
In this case
mysql> insert into tester values ('2003-02-29 00:05:00');
Query OK, 1 row affected (0.07 sec)
Notice that MySQL doesn't complain about the date.
Checking to see what MySQL enters you see that it just changed the date to
the first day in March.
mysql> select * from tester;
+----------------+
| time
|
+----------------+
| 20030301000500 |
+----------------+
1 row in set (0.00 sec)
Timestamps
Oracle
This will work,
INSERT INTO tablename (columnname)
VALUES (TIMESTAMP'2003-02-28
00:05:00')
while this will fail:
INSERT INTO tablename (columnname)
VALUES (TIMESTAMP'2003-02-29 00:05:00')
Concatenation
SQL Standard symbol for concat is ||
MySQL uses || as the OR operator
Uses concat(string1, string2)
More familiar to programmers
Breaks the standard
Oracle uses || for concat
Ex. string1 || string2
Which is better?
MySQL
For non mission-critical environments
Great for database enabled websites
Attractive price point
Oracle
Rock solid dependability, reliability, and features
Steep learning curve and expensive
Designed with the enterprise in mind
