Database engine

A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from adatabase. Most database management systems include their own application programming interface (API) that allows the user to interact with their underlying engine without going through the user interface of the DBMS.

The term database engine is frequently used interchangeably with database server or database management system.

Many of the modern DBMS support multiple storage engines within the same database. For example, MySQL supports InnoDB as well as MyISAM.

Some storage engines are transactional.

Name	Free	Transactional
Aria	GPL	No
BlitzDB	GPL	No
Falcon	GPL	Yes
InnoDB	GPL	Yes
MyISAM	GPL	No
InfiniDB	CPL	No
TokuDB	N/A	Yes
XtraDB	GPL	Yes

InnoDB is the default storage engine for MySQL as of MySQL 5.5. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). It is included as standard in most binaries distributed by MySQL AB, the exception being some OEMversions.

InnoDB became a product of Oracle Corporation after its acquisition of Innobase Oy in October 2005.^[1] The software is dual licensed; it is distributed under the GNU General Public License, but can also be licensed to parties wishing to combine InnoDB in proprietary software.^[2]

MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5.^[1] It is based on the older ISAMcode but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support. Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension.

Referance: WIKI

Read More...

MySQL: Datetime Versus Timestamp Data Types

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ’1000-01-01 00:00:00′ to ’9999-12-31 23:59:59′.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ’1970-01-01 00:00:01′ UTC to ’2038-01-19 03:14:07′ UTC.

A major difference between these two data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime data type values are unchanged in relation to time zone.

This example is a good exercise in demonstrating the difference between these two data types.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    |  Value              |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+
2 rows in set (0.00 sec)

You can see our current time zone information. Under this environment, let us create a table with the two data types and populate it with the same temporal information.

create table datedemo
(
 mydatetime datetime,
 mytimestamp timestamp
);

Query OK, 0 rows affected (0.05 sec)

insert into datedemo values ((now()), (now()));

Query OK, 1 row affected (0.02 sec)

select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

At this point the datetime and timestamp data types have remained the exact same values. Let us change the time zone see the results.

SET TIME_ZONE = "america/new_york";

Query OK, 0 rows affected (0.00 sec)

 select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The above example shows how the TIMESTAMP date type changed the values after changing the time-zone to ‘america/new_work’ where DATETIME is unchanged.

Read More...