Category Archives: My SQL

MySQL Limitations

When MySQL is used with standard tables (table type MyISAM), then locking, that is, the temporary blocking of access to or alteration of database information, is in operation only for entire tables (table locking). You can circumvent the table-locking problem by implementing transaction-capable table formats, such as InnoDB, that support row locking.

In using MyISAM tables, MySQL is not able to execute hot backups, which are backups during operation without blocking the tables with locks. Here again, the solution is InnoDB, though here the hot backup function is available only in the form of a commercial supplement.

Many database systems offer the possibility of defining custom data types. MySQL does not support such functionality, nor is any currently planned.

MySQL has up to now ignored the general XML trend. It is not clear when MySQL will support direct processing of XML data. Numerous commercial database systems offer considerably more functionality in this area, and even the SQL:2003 standard provides for a host of XML functions.

MySQL is in fact a very fast database system, but it is very limited in its usability for real-time applications, and it offers no OLAP functions. OLAP stands for online analytical processing, and refers to special methods for the management and analysis of multidimensional data. OLAP-capable database systems are often called data warehouses.

MySQL supports, since version 5.0, stored procedures and triggers, but these functions have not yet fully matured (this applies especially to triggers) and do not yet have the same stability and plenitude of functions offered by commercial database systems.

Similar restrictions hold as well for the GIS functions introduced in version 4.1. Commercial database systems offer in some cases considerably greater functionality.

MySQL MyISAM vs Innodb

MyISAM

  • Not *ACID compliant and non-transactional
  • MySQL 5.0 Default Engine
  • Offers Compression
  • Requires full repair/rebuild of indexes/tables
  • Changed Db pages written to disk instantly
  • No ordering in storage of data
  • Table level locking

Innodb

  • *ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
  • Rackspace Cloud Default Engine
  • Offers Compression
  • Auto recovery from crash via replay of logs
  • Dirty pages converted from random to sequential before commit and flush to disk
  • Row data stored in pages in PK order
  • Row level locking

CURTIME() Vs NOW()

CURTIME() returns the TIME part of the current time.
NOW() returns the date and time portions as a timestamp in various formats, depending on how it was requested

Example

mysql> SELECT NOW(),CURDATE(),CURTIME();
+———————+————+———–+
| NOW() | CURDATE() | CURTIME() |
+———————+————+———–+
| 2012-06-18 14:14:01 | 2012-06-18 | 14:14:01 |
+———————+————+———–+
1 row in set (0.03 sec)

The difference between MySQL CURTIME() and NOW()

CURTIME() returns the TIME part of the current time.
NOW() returns the date and time portions as a timestamp in various formats, depending on how it was requested

Example

mysql> SELECT NOW(),CURDATE(),CURTIME();
+———————+————+———–+
| NOW() | CURDATE() | CURTIME() |
+———————+————+———–+
| 2012-06-18 14:14:01 | 2012-06-18 | 14:14:01 |
+———————+————+———–+
1 row in set (0.03 sec)