Derby vs MySQL

Derby vs MySQL

This document describe about the differences about the DBMSs Apache Derby and MySQL.

1. Operating system support

OS\DBMS	Derby	MySQL
Windows	Yes	Yes
Mac OS	Yes	Yes
Linux	Yes	Yes
Unix	Yes	Yes
Amigo OS	No	Yes
Symbian	No	Yes

MySQL supports all most of the OS than the Apache Derby. The reason is Apache Derby need java to run it.

2. Fundamental fetchers

Fetcher\ DBMS	Derby	MySQL
ACID	yes	yes
Referential Integrity	yes	yes
Transactions	yes	yes
Unicode	yes	partial
Interface	SQL	SQL
DBMS\feacher	Derby	MySQL
ACID	yes	yes

ACID- Atomicity/ Consistency/ Integrity/ Durability

MYSQL partially support the Unicode and Apache Derby fully supports the Unicode. MySQL supports- UCS-2 encoding /UTF-8 encoding.

3. Limits

fetcher \DBMS	Derby	MySQL
Max DB size	?	unlimited
Max table size	?	MyISAM 256TB
Max row size	?	64KB
Max column per row	1012(5000 in view)	4096
Max blob/clob size	2,147,483,647 chars	4GB ( long text/long blob )
Max char size	254 varchar	32672
Max number size	?	64bits
Min DATE	0001-01-01	1000
Max DATE	31/12/9999	9999
Max column name size	128	64
Max number size	?	64bits

We can see here there are number of limitations of Apache Derby than MySQL. The main reason for this is the limitations of java. For example,

Because of java array size limit is 231

I. Number of chars in names are limited

II. Number of row s per table is limited

III. Number of column per table is limited

IV. Characters in data types like CHAR,VARCHAR is limited.

4. Tables and views

DBMS	Derby	MySQL
Temp table	Yes	Yes
Materialized view	No	No

Both DBMSs are not capable of creating materialized views. But materialized view is very important when using DBMS in data warehousing.

5. Indexes

DBMS	Derby	MySQL
R-/R+ tree	No	MyISAM only
Hash	No	Memory, Cluster(NDB), InnoDB tables only
Expressions	No	No
Partial	No	No
Reverse	No	No
Bitmap	No	No
GIST	No	No
GIN	No	No

6. Database capabilities

DBMS	Derby	MySQL
Union	Yes	Yes
Intersect	Yes	No
Except	Yes	No
Inner join	Yes	Yes
Outer join	Yes	Yes
Inner selects	?	Yes
Merge join	?	Yes
Blobs and Clobs	Yes	Yes
Common table expressions	No	No
Windowing function	No	No

7. Other objects

DBMS	Derby	MySQL
Data domain	No	No
Cursor	Yes	Yes
Trigger	Yes	Yes
Function	Yes	Yes
Procedure	Yes	Yes
External routing	Yes	Yes

8. Partitioning

DBMS	Derby	MySQL
Range	No	Yes
Hash	No	Yes
Composite(Range+Hash)	No	Yes
List	No	Yes
Shadow	?	?
Native Replication API	?	?

9. Data types

DBMS	Derby	MySQL
Type system	Static	Static
Integer	SMALLINT(16 bits) INT(32 bits) BIGINT(64 bits)	TINYINT(8 bits) SMALLINT(16 bit) MEDIUMINT(24 bit) INT(32 bit) BIGINT(64 int)
Floating point	FLOAT(32 bits) DOUBLE(64 bits) REAL(32 bits)	FLOAT(32 bit) DOUBLE(64 bit)
Decimal	DECIMAL NUMERIC	DECIMAL
String	CHAR VARCHAR CHARFORBITDATA LONGVAR- CHARFORBITDATA CLOB	CHAR VARCHAR TEXT
Binary	BLOB	BLOB
Date/Time	DATE TIME TIMESTAMP	DATETIME DATE TIMESTAMP YEAR
Boolean	BOOLEAN	BOOLEAN
Other	SET	ENUM SET

10. Access control

DBMS	Derby	MySQL
Native network encryption	Yes (can use with or without)	Yes (SSL with 4.0)
Brute-force protection	Yes	No
Enter price directory compatibility	Yes	No
Password complexity rules	?	No
Patch access	?	Partial No security page
Run unprivileged	Yes	Yes
Audit	?	?
Resource limit	?	?
Separation of duties between admin/ operator backup like RBAC	?	?
Security certification	No	No