Table Types in Mysql

Understanding MySQL Table Types

MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:

• ISAM
• MyISAM
• InnoDB
• BerkeleyDB (BDB)
• MERGE
• HEAP

The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:

ISAM

ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.

MyISAM

MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.

InnoDB

Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.

BDB

BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.

MERGE

Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.

HEAP

Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.

Cakephp vs Zend

Feature	CakePHP	Zend Framework	Notes
License	MIT	BSD	Each of these licenses is fairly flexible. Carefully consider how your application will be used and distributed. It's also important to consider compatability with 3rd-party extensions included in your application.
Compatability	4 and 5	5.1.4 or later	I'm curious to know what CakePHP's PHP 4 support plans are. I would wager that Zend's focus on PHP 5 provides it with performance advantages.
Documentation	good	excellent	CakePHP's documentation is good but Zend's API documentation is more thorough.
Community	Google group, IRC, Articles	Wiki, Lists, Chat	Both frameworks have active user communities. In addition to official channels, there are also several 3rd-party community sites easily found through Google. CakePHP has done an excellent job of marketing their framework considering that they don't have the corporate backing that ZF has.
Tutorial/Sample Availability	excellent	fair	CakePHP's Bakery provides an extensive collection of user-submitted applications and code samples. The IBM developerWorks CakePHP tutorials and aritcles are excellent. The Zend tutorials I've reviewed are good but require a siginifcant amount of MVC design experience. The ZF beginner's videos, however, are very helpful. CakePHP provides screencasts too.
MVC	strict	optional	These points are the biggest distinctions between these frameworks and probably the most important factors when choosing one over the other. CakePHP has very strict naming and code organization conventions while Zend only enforces conventions when employing its MVC capabilities.
Conventions	strict	flexible
Configuration	PHP file	PHP Array, XML, or INI files	This difference doesn't seem like a sticking point to me. If you're a fan of build tools like ANT, you might prefer Zend's choice of XML.
Database Abstraction	PHP, PEAR, ADODB	PHP, PDO	I may be wrong, but I get the impression that PDO is gaining favor in the PHP community.
Security	ACL-based	ACL-based	Both frameworks take security very seriously and provide authentication and ACL-based authorization. Both approach ACLs in a similar fashion and both provide a high degree of flexibility in creating and applying ACLs.
Data Handling	good	excellent	Out of the box, both frameworks provide data validation and sanitization mechanisms. Zend provides more validation options, but CakePHP's validation is easily extended via PCRE. CakePHP provides a single data sanitization mechanism where Zend provides various filters to run data through.
Caching	good	excellent	Both provide file-based caching of pages. Zend supports several cache backends, including APC, SqlLite, and of course, the Zend Platform.
Sessions	excellent	excellent	Both provide robust session handling.
Logging/ Debugging	good	excellent	Both provide application logging. IMHO, the Zend_Debug class gives ZF the edge in this category.
Templating	PHP-based	PHP-based	Coming from a UI design background, templating is of particular interest to me. If you've developed templates for WordPress or Drupal, you'll feel right at home with CakePHP. The Zend Framework requires a bit more work to get templating up and running (see Zend_View and Zend_Layout). It's possible to integrate 3rd party templating engines, like Smarty, with each framework.
Helpers	good	excellent	Both frameworks provide basic helpers to handle AJAX, forms, and time conversions. CakePHP provides a nice HTML helper which is absent in ZF but ZF provides a wider range of helper classes.
JavaScript/Ajax	good	fair	CakePHP comes with built-in support for Prototype and script.aculo.us while the current stable release of the ZF doesn't support any specific JavaScript framework. ZF does provide JSON support and the 1.5 release adds AJAX form featues. I'd like to see both provide a wider range of support for 3rd party JavaScript Frameworks, particularly Jquery and YUI.
Web Services	good	excellent	Both provide support for REST and XML-RPC as well as basic XML feed parsing. Zend Framework also provides support for several popular web services including OpenID, Reflection, Akismet, Amazon, Audioscrobbler, Delicious, Flickr, Simpy, StrikeIron, Technorati, and Yahoo.
Localization	good	excellent	Both support localization through stadard means (i18n, l10n). CakePHP is adding/improving support in their upcoming 1.2 release. Zend appears to have better support for localization and translation.
Unit Testing	yes	yes	Both frameworks provide support for this feature that far too many of us avoid ;) I have yet to evaluate unit testing support.

Indexing

Ques -: What are advantages and disadvantages of indexes in MySQL?

Advantages of MySQL Indexes

Generally speaking, MySQL indexing into database gives you three advantages:

* Query optimization: Indexes make search queries much faster.
* Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
* Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.

Disadvantages of MySQL indexes

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.