Difference between InnoDB and MyISAM

InnoDB and MyISAM both are very important engine type in MYSQL.InnoDB and MyISAM both have their unique advantages in their favor and disadvantages against each other.Each of them is more suitable in some scenarios than the other.

InnoDB features:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

MyISAM features:
table-level locking
fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

Posted: Wednesday, April 10th, 2013
Author: Parvez

How to Check MYSql Indexes Size on Database

The Index is very important machanism to improve mysql performance but some cases index size could be increase than available memory for the application.So first question in mind how to check how much RAM available for index and how much consume memory your database index.In this tutorial i will described for two type engine InnoDB and MyISAM.

MyISAM

Available RAM for indexes :
We can get Available RAM for indexes through below MySQL variable:
key_buffer_size
Required Memory for Index:
We can get Required RAM for indexes with help of below SQL:
SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMBInDB
FROM information_schema.tables WHERE engine=’MyISAM’ AND
table_schema NOT IN (‘information_schema’,'performance_schema’,'mysql’);

InnoDB

Available RAM for indexes :
We can get Available RAM for indexes through below MySQL variable:
innodb_buffer_pool_size
Required Memory for Indexes:
We can get Required RAM for indexes with help of below SQL:
SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB
FROM information_schema.tables WHERE engine=’InnoDB’;

Posted: Monday, March 25th, 2013
Author: Parvez

How to Create View in MySQL

View is very important factor of Database based on performance. View is virtual table that’s contains result set of your SQL statement. In SQL statement yo can use SQL functions, WHERE, and JOIN statements to get result set.
The View table can contain rows and column like your original table the column name may be different and unique in VIEW table. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. The Columns name in view may be one or more real tables in the database.

Syntax:

1
2
3
4
5
6
7

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

A view can belong to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, specify the name as db_name.view_name:
Syntax would be:

1
2
3
4
5
6
7

CREATE VIEW test.view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Where: test is the database name

Posted: Saturday, February 9th, 2013
Author: Parvez

How To Create Index in MySQL

Today in web application performance is main issue. So performance can improved through programming side or database side. In this tutorial I will describe how to improve performance of your application database side. Index is very important factor to improve performance.
When the application has too much data and updated frequent data, which is based on condition then you need to define index on table column.

1

Alter table `operationdatafield` ADD INDEX (`OperationId`,`FieldName`,`Period`) ;

addindex

Posted: Wednesday, September 19th, 2012
Author: Parvez

MySQL :: #2002 – The server is not responding

Hi,
Today I am getting problem in mysql server, When I was click phpmyadmin under wamp server, I am getting below error.
#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured).

Socket message


After couple of hours of head scratching, I have got answer and cracked it.
Problem occurred when mysql socket property is not set in config file.I have solved this problem of WAMP server environment.
The following steps needed when you will face above problem.
Step1: Change php.ini file setting

1
2
3
4
5

mysql.default_socket =
To
mysql.default_socket =/tmp/mysql.sock

Step2: Sometimes above setting is not reflected , so you need to change php.ini setting under status bar wamp server.

wamp setting

1
2
3
4
5

mysql.default_socket =
To
mysql.default_socket =/tmp/mysql.sock

Step3: change C:\wamp\apps\phpmyadmin3.3.9\config.inc setting.

1
2
3
4
5

$cfg['Servers'][$i]['socket'] = '';
To
$cfg['Servers'][$i]['socket'] = '/tmp/mysql.sock';

Thank you for taking the time to read my post.

Posted: Tuesday, September 18th, 2012
Author: Parvez

How To add/Update FOREIGN KEY Constraints

In this post I will described how to alter table and add new FOREIGN KEY constraints on table.Its very easy 3 steps work. After that you can add predefined trigger like on Delete, On updated, Not set etc.
Which is very useful if you want remove/update parent data with all relationship data which is stored in child table.
mysql-constraints


Step 1: first we add Column in child table.

ALTER TABLE Role ADD COLUMN Parent int(11) UNSIGNED DEFAULT NULL;

Step 2: first we add Index on foreign Key, because MySQL says foreign Key must Index.

ALTER TABLE Table1 ADD INDEX ParentTabl(ParentID)

Step 3: Now alter table and add constraints.

ALTER TABLE `ChildTbl` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES `ChildTbl`(`Id`)

Step 4: Now define trigger if you want add with constraints.
ALTER TABLE ` ChildTbl ` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES ` ChildTbl `(`Id`) ON DELETE CASCADE;

Note: If you are finding ‘Errno 150’ thats mean your PK column(s) in the parent table and the FK column(s) in child table is not same data type.

Posted: Monday, September 17th, 2012
Author: Parvez

Constraints Type in MySQL

MySQL CONSTRAINT is very useful when to define rule for columns. Its restrict the column what values can be store. Its apply in table for enforce integrity of database. MySQL CONSTRAINTS can be apply on column and table.The column level constraints can apply only to one column whereas table level constraints are applied to the entire table.

mysql-constraints

CONSTRAINT DESCRIPTION
NOT NULL In Mysql NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE The UNIQUE constraint in Mysql does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster.
FOREIGN KEY A FOREIGN KEY in mysql creates a link between two tables by one specific column of both table. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT In a Mysql table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.
Posted: Saturday, September 15th, 2012
Author: Parvez

How To Get Mysql Database Size In MB

In this Tutorial i will describe how to get database size in MB.Its very important script when you want list of all available database in mysql server with size in MB based on current logged-in user.

mysql_database_size

The data_length + index_length is equal to the total table size.

data_length – store the real data.
index_length – store the table index.
below is the SQL script to list out the entire databases size

1
2
3

SELECT table_schema "scg_insights_app", SUM( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema

Above code gives each database record from your MySQL scheme for the current logged in user.The size of database is sum of the data and index length to show a grand total.

Posted: Tuesday, September 11th, 2012
Author: Parvez

Referential Actions In Mysql

Referential integrity is playing very important role when you creating in huge database. When an UPDATE or DELETE operation affects of parent table that has matching rows in the child table. The result also affect child table depends on the referential action specified using ON UPDATE and ON DELETE subclauses of theFOREIGN KEY clause on table.
InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.


• CASCADE : Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.
Note
Currently, cascaded foreign key actions do not activate triggers.

• SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

• NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. InnoDB rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

• SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

InnoDB supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, “child table records” really refers to dependent records within the same table.

Refrence Taken From Mysql.com

Posted: Monday, September 10th, 2012
Author: Parvez

How To Define Constraints In Table :MySQL

Now days normally table must be have foreign key and primary key relation, So when you defining the foreign key in a table you should also define foreign Key constraint on child table.
With help of Constraint the user cannot delete parent data unless he will not delete all dependent parent data from child table. InnoDb support foreign keys Constraints.

It type of security which is apply on table level in database.
Below code is used to create a table and define FOREIGN KEY with FOREIGN KEY Constraint.

Rules to define constrains:

1. The PK column(s) in the parent table and the FK column(s) must be the same data type.
2. The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
3. The two tables must have the same charset.
4. The two tables must be ENGINE=InnoDB.
5. If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.

Today I will described in simple example how to create table with FOREIGN KEY Constraints.

Syntax:
FOREIGN KEY (parentId) REFERENCES parent(parentId)

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE IF NOT EXISTS `operationdatafield` (
  `DataFieldId` int(11) NOT NULL AUTO_INCREMENT,
  `OperationId` int(11) NOT NULL,
  `FieldName` varchar(255) NOT NULL,
  `CreatedBy` varchar(50) NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ModifiedBy` varchar(50) NOT NULL,
  `ModifiedOn` datetime NOT NULL,
  `DependentField` varchar(50) NOT NULL,
  PRIMARY KEY (`DataFieldId`),
FOREIGN KEY (OperationId) REFERENCES Operation(OperationId)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;