Federated tables allow access to the tables located on other databases on other servers.
Tag Archives: MySQL
MySQL MyISAM Static And MyISAM Dynamic
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.
MySQL myisamchk
myisamchk compress the MyISAM tables, which reduces their disk or memory usage.
MySQL Primary Key And Candidate Key
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
MySQL FLOAT And DOUBLE
Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
MySQL Heap Tables
HEAP tables are present in memory and they are used for high speed storage on temporary
- BLOB or TEXT fields are not allowed
- Only comparison operators can be used =, <,>, = >,=<
- AUTO_INCREMENT is not supported by HEAP tables
- Indexes should be NOT NULL
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
Create Database
Run below command to create a Database
mysql> create database magento;
Query OK, 1 row affected (0.01 sec)
WordPress MySQL Query Delete All Trash Comments
delete from wp_comments where comment_approved=’trash’;