All posts by Pramod T P

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

Apache Directives

Apache directives are a set of rules which define how your server should run, number of clients that can access your server, etc. you can change them by editing the httpd.conf and related files to meet your requirements

Below given are the some of the important directives

  • Alias
  • AliasMatch
  • CheckSpelling
  • DocumentRoot
  • ErrorDocument
  • Options
  • ProxyPass
  • ProxyPassReverse
  • ProxyPassReverseCookieDomain
  • ProxyPassReverseCookiePath
  • Redirect
  • RedirectMatch
  • RewriteCond
  • RewriteRule
  • ScriptAlias
  • ScriptAliasMatch
  • UserDir