Difference between MyISAM and InnoDB Engine

1. InnoDB supports foreign keys where MyISAM does not.
2. MyISAM use table level locking and InnoDB supports row level locking.
3. MyISAM are probably best suited for Data marts and small specialized data warehouses,InnoDB would be a good solution for say a real time data warehouses where the inserts need to be more frequent than daily
4. MyISAM beter to use for tables that have alot of SELECT transactions and don't have alot of INSERT or UPDATE queries, but InnoDB better for opposite situation.
5. MyISAM is easy to recover but InnoDB not
6. MyISAM quite easy in maintenance than InnoDB.
7. MyISAM is prone to corruption than innodb.

As a general ACID compliant DB engine the InnoDB seems to be best bang for the buck, but when used in scale has fairly high resource costs and still have concurrency issues with manipulating large data sets. MyISAM use table level locking and have no transaction support it makes this engine unsuitable for either OLTP or larger data warehousing cases. MyISAM needs to be used in cases where your active dataset cannot fit into system memory.

• MyISAM is a disk based storage engine. Aiming for very low overhead, it does not support transactions.
• InnoDB is also disk based, but offers versioned, fully ACID transactional capabilities. InnoDB requires more disk space than MyISAM to store its data, and this increased overhead is compensated by more aggressive use of memory caching, in order to attain high speeds.
• Memory (formerly called "HEAP") is a storage engine that utilizes only RAM. Special algorithms are used that make optimal use of this environment. It is very fast.
• NDB, the MySQL Cluster Storage engine, connects to a cluster of nodes, offering high availability through redundancy, high performance through fragmentation (partitioning) of data across multiple node groups, and excellent scalability through the combination of these two. NDB uses main-memory only, with logging to disk.

If you use CREATE TABLE without specifying the ENGINE option, the server will use the default. The default storage engine is MyISAM.
If you want to change the default to say InnoDB, you can use the configuration directive default-storage-engine=InnoDB.

You can change the storage format of an existing table.
ALTER TABLE t1 ENGINE=InnoDB;

These commands make the server create the proper table structure, indexes, and copy all the data. Do note that not all storage engines support all column and index types, so conversion is not practical in all instances.

MyISAM
MyISAM has been the default storage engine since version 3.23. With MyISAM, each database is a directory, with each table stored in a separate set of files.
For an individual table, the .frm file contains information about the table structure — effectively, an internal representation of the CREATE TABLE statement. The .MYD file contains the row data, and the .MYI contains any indexes belonging with this table, as well as some statistics about the table. The data file contains only row data, with minimal overhead.

MyISAM actually offers a number of storage and indexing options within its basic file format. We will cover the ones that are most commonly used, so that we can compare them with each other and the characteristics of the other storage engines.

The MyISAM "fixed" row format is automatically selected if the table structure does not contain any VARCHAR, TEXT or BLOB type columns. In this case, each column has a fixed length, and as a consequence, each row will be the same length. This means that if any rows are deleted, they will only ever be replaced by rows of the exact same length, so rows never become fragmented. Also, the storage engine will be able to access individual rows by their "row number" instead of a complete file offset. This makes any indexes smaller, and the overall system faster and more memory efficient. Please note that the "row number" mentioned here is purely internal, as rows in relational database tables are by definition unordered.


InnoDB
The InnoDB storage engine has a completely different architecture. InnoDB uses the concept of a tablespace, where all structure, table data and indexes are stored. A tablespace can consist of one or more files, even raw disk partitions. Recent versions of MySQL also offer an alternate mode, where InnoDB stores information belonging to a specific table in a single file, i.e. One file per table.
Concurrency control in InnoDB is handled by its complete support ACID transactions, multi-versioning, row-level locking, and foreign key constraints. For an in-depth discussion of these characteristics, see the article on "Concurrency Control".

How PHP Sessions Work

How Sessions Work?
Sessions in PHP are started by using the session_start( ) function. Like the setcookie( ) function, the session_start( ) function must come before any HTML, including blank lines, on the page. It will look like this:

<?php
session_start( );
?>

The session_start( ) function generates a random Session Id and stores it in a cookie on the user's computer (this is the only session information that is actually stored on the client side.) The default name for the cookie is PHPSESSID, although this can be changed in the PHP configuration files on the server (most hosting companies will leave it alone, however.) To reference the session Id in you PHP code, you would therefore reference the variable $PHPSESSID


Every session in PHP begins with a call to the session_start() function. This function checks to see whether a session already exists, and either restores it (if it does) or creates a new one (if it doesn't). Session variables can then be registered by adding keys and values to the special $_SESSION superglobal array, and can be accessed at any time during the session using standard array notation.

<?php

// initialize a session
session_start();

// increment a session counter
$_SESSION['counter']++;

// print value
echo "You have viewed this page " . $_SESSION['counter'] . " times";

?>
In the example above, a key named counter has been added to the $_SESSION array. The first time a session is created, this key will have the value 0. On every subsequent request for the page during the same session, the previous value of the counter will be retrieved and incremented by 1. If the example above doesn't work as advertised, check to make sure that the session.save_path variable in your php.ini file points to a valid temporary directory for your system. This value is hard-wired to /tmp by default, so if you're trying the example on a Windows system, you will need to edit it to C:\Windows\temp (or your system's temporary directory).

what happens when you come to the second pass through your page and reach the session_start( ) function again. PHP knows that there is already a session on progress and so ignores subsequent instances of the session_start( )

Session variables are created by registering them for the session, using the session_register( ) function.
When the user shuts down the client browser it destroys the session, the $_SESSION array will be flushed of all session variables. You can also explicitly destroy a session - for example, when a user logs out - by calling the session_destroy() function.

It's important to note that the call to session_start() must appear first, before any output is generated by the script. This is because the PHP session handler internally uses in-memory cookies to store session data, and the cookie creation headers must be transmitted to the client browser before any output.If you see a warning generated in PHP enabled page

Warning: Cannot send session cache limiter - headers already sent (output started at ...)

because somewhere, somehow, some output has found its way to the browser before session_start() was called. Even a carriage return or a blank space outside the PHP tags surrounding session_start() can cause this error.

Every session has a unique session ID. PHP uses to keep track of different clients through sesion ID.The session ID is a long alphanumeric string, which is automatically passed by PHP from page to page so that the continuity of the session is maintained.

What's the Difference Between Storing Your Data in Cookies and in Session Variables?

• Cookies are returned and stored in the user's browser, session data are stored on your web server.
• The life span of a cookie can be set to almost any duration of your choosing. PHP sessions have a predetermined short life. The exact life span depends on how your web host has configured PHP on your server.
• Depending on how your web server is configured, session data is often stored in a public temporary directory on the server. As such it is possible that other users on the server may be able to peek at the data you store there.

PHP Session Cookies Across Subdomains and Multiple Domains

PHP Session Cookie Multiple Domains

You need to change the php session configuration option for session.cookie_domain from the default of “” (which inserts your hostname) to:

“.domain.com”

You can do this with: session_set_cookie_params() before doing your session_start() or if you have php start your sessions for you automatically you might consider throwing:

php_value session.cookie_domain ".domain.com"into the .htaccess file for the site. If you put the .htaccess file in the directory for xxx.domain.com then all sessions started on xxx.domain.com will be shared on all other domains like www.domain.com. This means however that sessions started in www.domain.com won’t carry over unless the .htaccess file is also present in it’s root directory.

Important Note

The first dot in “.domain.com” is not always nessesary however for support of all browsers it is suggested.




session_cache_expire
session_cache_expire() returns the current setting of session.cache_expire.
The cache expire is reset to the default value of 180 stored in session.cache_expire at request startup time. Thus, you need to call session_cache_expire() for every request (and before session_start() is called).


session_cache_limiter
session_cache_limiter() returns the name of the current cache limiter.
The cache limiter defines which cache control HTTP headers are sent to the client. These headers determine the rules by which the page content may be cached by the client and intermediate proxies. Setting the cache limiter to nocache disallows any client/proxy caching. A value of public permits caching by proxies and the client, whereas private disallows caching by proxies and permits the client to cache the contents.
In private mode, the Expire header sent to the client may cause confusion for some browsers, including Mozilla. You can avoid this problem by using private_no_expire mode. The Expire header is never sent to the client in this mode.
The cache limiter is reset to the default value stored in session.cache_limiter at request startup time. Thus, you need to call session_cache_limiter() for every request (and before session_start() is called).

session_decode
session_decode() decodes the session data in data, setting variables stored in the session.

session_destroy()
destroys all of the data associated with the current session. It does not unset any of the global variables associated with the session, or unset the session cookie. To use the session variables again, session_start() has to be called.

session_encode()
returns a string with the contents of the current session encoded within.


session_id()
is used to get or set the session id for the current session.
The constant SID can also be used to retrieve the current name and session id as a string suitable for adding to URLs.

session_is_registered
— Find out whether a global variable is registered in a session
//instead of session_register('VARNAME');
if(isset($_SESSION['VARNAME']))

session_module_name
Get and/or set the current session module

session_regenerate_id
— Update the current session id with a newly generated one

session_save_path
returns the path of the current directory used to save session data.
session_save_path('/home/example.com/sessions');
ini_set('session.gc_probability', 1);

session_set_cookie_params — Set the session cookie parameters


session_write_close
Session data is usually stored after your script terminated without the need to call session_write_close(), but as session data is locked to prevent concurrent writes only one script may operate on a session at any time.

MySQL Triggers

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table. For example, the following statements create a table and an INSERT trigger. The trigger sums the values inserted into one of the table's columns:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Query OK, 0 rows affected (0.06 sec)

Important:

MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API. Restrictions on Stored Routines and Triggers

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.

All of the restrictions for stored functions also apply to triggers.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

• The table-maintenance statements CHECK TABLES and OPTIMIZE TABLES. This restriction is lifted beginning with MySQL 5.0.17.
• The locking statements LOCK TABLES, UNLOCK TABLES.
• LOAD DATA and LOAD TABLE.

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. CREATE TRIGGER was added in MySQL 5.0.2. Currently, its use requires the SUPER privilege.

The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

When the trigger is activated, the DEFINER clause determines the privileges that apply, as described later in this section.

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after the statement that activated it.

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following:

· INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

· UPDATE: The trigger is activated whenever a row is modified; for example, through UPDATE statements.

· DELETE: The trigger is activated whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. However, DROP TABLE and TRUNCATE statements on the table do not activate this trigger, because they do not use DELETE.

It is important to understand that the trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger is activated by not only INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

There cannot be two triggers for a given table that have the same trigger action time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 

  b4 INT DEFAULT 0

);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

  FOR EACH ROW BEGIN

    INSERT INTO test2 SET a2 = NEW.a1;

    DELETE FROM test3 WHERE a3 = NEW.a1;  

    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

  END;

|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 

  (NULL), (NULL), (NULL), (NULL), (NULL), 

  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 

  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);