Site Moved

This site has been moved to a new location - Bin-Blog. All new post will appear at the new location.


MySQL Storage Engines - MyISAM, InnoDB and others

MySQL has a feature called Storage Engines - it supports multiple independent storage engines that can be used as handlers for different table types. Each table can be stored in a way that is most optimized for that particular table.

With this system, the developer can choose how a table must be stored - he can chose which storage engine is best for that particular situation. For example in a content management system, the table containing the contents will be read more often - so we will chose a storage engine better optimized for reading data(eg. MyISAM). But in the case of the table storing the logs, it must be written more often - so we chose a storage engine that is better at writing for that table. Having multiple storage engine makes sure that you can get the best optimization for any given situation.

There are many who believe this is a Bad Thing. In most other DBMS, the Administrator don't have to worry about such details - the DBMS will take care of this. I don't want to take sides in this debate - as for many people, this has become a religious issue. Anyway, as I am writing a series on MySQL, I cannot ignore this very important feature of MySQL.

I have only used two of MySQL many Storage Engines - MyISAM and InnoDB. The rest, I have not uses yet. Someday.


MyISAM is the default storage engine for MySQL. If you create a table in MySQL, without paying any attention to select a specific engine, then that table will be created in MyISAM(by default). This storage engine is the best for high-speed storage and retrieval. It also supports fulltext searching capabilities.

MyISAM is supported in all MySQL configurations. This engine is used for Data warehousing, read-only archives optimized for bulk operations, etc. as it is the best solution when you want flexible index formats, big scans and has a small disk footprint for data and index. This is not suitable for a typical OLTP application because of table-level locking, bad crash durability and lack of ACID compliance.


The InnoDB storage engine provide transaction-safe tables(BDB also does this). InnoDB is also included by default in all MySQL 5.0 binary distributions. This engine supports...

  • Row level locking
  • MVCC
  • Savepoints within transactions
  • More features for typical OLTP applications.

One of the main advantage of this engine is that you can rollback to a previous version of the database using its COMMIT/ROLLBACK support. InnoDB engine is more reliable than MyISAM.


The MEMORY storage engine provides in-memory tables. The MERGE storage engine will let you handle many identical MyISAM tables as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both are also included in MySQL by default.

The main advantages of the MEMORY Storage engine are...

  • No disk footprint
  • Extremely fast

This is most useful for...

  • Summary tables
  • Simulations
  • Temporary operations(sessions etc)


The CSV storage engine stores data in text files using comma-separated values format. The main advantages of this approach are...

  • Very simple - data stored in plain text files
  • The database can be modified without the server
  • Export/Import couldn't be easier.

Other Storage Engines

There are many other storage engines supported by MySQL. Some of them are...

  • NDB Cluster
  • Falcon

If you are not satisfied with any of these engines, you can write your own engine.



Live Validator - JavaScript Field Validation Script

Live Validator is a JavaScript program that will validate any form field as the user types the data using the regular expressions that you have specified.


Key Validation
Valides the key as you type it. For example, if you just want numbers in a field, the user will not be allowed to enter alphabets.
Live Validation feedback
If the field validates, the color of the element will change
Attach a form to the validation.
This will show error when the form is submitted
Reasonable size
4.2 KB uncompressed
Works in most major browsers.
At least the ones I tested in - Firefox 1.5/Linux, Firefox 1.5/Windows, IE 6, Opera 9/Linux, Opera/Windows
And more...
Many more features...

phpMyAdmin Tips and Tricks - Database Backup and Restore

phpMyAdmin is a big software - there are features that I have not used yet. But there are some features that are not very obvious - but could save you a lot of time.

Quick Table Browse

The normal way to seeing the contents of a table is to click on the table name in the left pane and then click on the 'Browse' tab in the top. But there is a one click way to do this - click on the small icon next to the table name in the left pane.

Click icon to browse table

Default Table Selection

When you access phpMyAdmin, you get the main page - after that you have to chose from the drop down which database should be used. Wouldn't it be useful if the database you use most would be automatically selected on loading? You can do this by accessing phpMyAdmin using the following URL.


You will have to change the database name and the location to the one in your system - but you got the idea right?

Database Backup

There are many ways to take a database backup using phpMyAdmin - but my favorite way is to take an SQL Dump.

Select the database you want to backup and click 'Export' tab in the main frame. Now select all the tables in the select box. You can select the format of the backup - like SQL, CSV, XML etc. We will select the SQL option. It is recommended that you check the 'Add DROP TABLE' and 'Add IF NOT EXISTS' checkboxs. This will make sure that the existing tables will be removed when restoring the backup. Now check the 'Save as file' checkbox. Then click the 'Go' button. Now you should see a download option for the database backup. You can save this file to you harddisk and run it later if you want to restore the backup. If you did not check the 'Save as file' option, the SQL dump will be shown in a textarea.

This can also be done using the 'mysqldump' command in linux using the following command.

mysqldump --add-drop-table --user=root DATABASE> backup_file.sql

Table Backup

If you just need the backup of a single table, select that table from the table list in the left pane and click the 'Export' tab. Now do the steps described in the database backup section. Here only the selected table will be backuped.

This can be done using the following command.

mysqldump --add-drop-table --user=root DATABASE TABLE> backup_file.sql

Restoring a backup

Click the 'SQL' icon(SQL Icon) in the left pane - this will open a popup. Paste the SQL code you when you backuped the database in the textarea in this popup. Alternatively, you can chose the 'Import Files' tab in this popup and chose the location of the file you saved when backing up the data.

This is not a recommended method to restore large backup - if the backup file is more than 1 MB, don't try this. A better method is to upload the backup file to you host and restore the backup using the shell.


Installing phpMyAdmin 2.8

phpMyAdmin Logo

The install instructions I provided earlier will only work in the earlier versions of phpMyAdmin. The latest version( - as of Aug 15, 2006) has a much easier way of doing this.

Get the latest version of phpMyAdmin from The files will compressed - so you will have to extract it. Create a folder called 'phpMyAdmin' in the document root of your server and extract the PHP scripts into it. That's it - the installing part is over. The latest version will automatically configure itself to run with these settings...

  • Database Host : localhost
  • Username : root
  • Password : (Empty)
For the example we provided the last time, this is all that is needed. But if you wish to configure your phpMyAdmin using more advanced options, open the folder you installed phpMyAdmin to(<Document Root/phpMyAdmin/>) and create a folder called 'config'. Make sure it has write permission. You can give it write permission with the following command(assuming that your are on a linux system)...
chmod 666 config

Now open the URL http://localhost/phpMyAdmin/scripts/setup.php. This will help you configure your phpMyAdmin.

The configuration is very straight forward - so I am not providing much details about it. If you have any doubt, reffer the documentation that comes with the installation.

After you have finished configuration, click on the 'Save' Button in the 'Configuration' section. This will create a '' file inside the 'config' folder that we created earlier. You will have to manually copy this config file to you phpMyAdmin folder. After this is done, delete the config folder. You can do this with the command...

mv config/ .
rmdir config

On a personal note, I still prefer the 2.6 Version of phpMyAdmin. Some pages in the latest version have a tendency to create horizontal scroll bars even on 1024x768 resolution.


Installing and Configuring phpMyAdmin

phpMyAdmin Logo

In the last post, I introduced phpMyAdmin. In this, we will deal with installation and configuration of phpMyAdmin.

UPDATE: This installation manual is for the older versions on phpMyAdmin. To see how to install the latest version, see the post Installing phpMyAdmin 2.8

My aim is to create a tool that could be used to administer your database in a development server. These directions are NOT for a production server. You will need much more security when installing phpMyAdmin on a production server. A good source for information on how to install phpMyAdmin securely is the series 'Doing more with phpMyAdmin'.

Installation phpMyAdmin

Get the latest version of phpMyAdmin from The files will compressed - so you will have to extract it.

Create a folder called 'phpMyAdmin' in the document root of your server and extract the PHP scripts into it. That's it - the installing part is over.

Configuring phpMyAdmin

Necessary Information

Now we have to configure it - before starting this, make sure you have the following details...

  • Database Server address
  • Database Username
  • Database Password

For the sake of example I am going to assume that the following values...

  • Server : 'localhost'
  • Username : 'root'
  • Password : ''

Using the 'root' user with an empty password is one of the worst security blunders you can make - but what the hell, we are on a development server, right?

Editing ''

Open up the file '' in your favorite editor and edit the following values...

$cfg['PmaAbsoluteUri'] = 'http://localhost/phpMyAdmin/';

This is the absolute path for your phpMyAdmin directory. If you enter this location into a browser, phpMyAdmin should show up. I will not recommend that you use 'localhost' as the domain if you are on a network and expect others to connect to this phpMyAdmin setup. You should use 'http://<YOUR IP</phpMyAdmin/' in such a case. However if you are using phpMyAdmin for just your system, localhost will do fine.

$cfg['Servers'][$i]['host'] = 'localhost';

This is the database server address. If you look at the following lines in the file, you will notice that there is more than one instance of this line. This is for managing more that one database server using just one phpMyAdmin. Make sure that all the other hosts are set to empty - phpMyAdmin will ignore it if it is empty.

$cfg['Servers'][$i]['user'] = 'root';

The database user - in our case 'root'. Only the first instance of the line should be set - the other will be ignored if the 'host' option is not set.

$cfg['Servers'][$i]['password'] = '';

The password for the above given user - in this example we are using an empty password.

There are many other options - but you will not have to worry about them - they will do fine with their default values. However, if you are adventurous, feel free to experiment with the configuration options. The file well commented in detail - so you will have no trouble finding your way around - if you know a bit of PHP.

After all this is done, open up a browser and point it to http://localhost/phpMyAdmin/. If all went well, you will get the front page of phpMyAdmin.



Managing Databases with MySQL Clients - phpMyAdmin

There are many MySQL Clients(or database administration tools) out there - MySQL Front End, Tora, mysql etc. But in the web environment, one client rules - phpMyAdmin. phpMyAdmin is a PHP based(obviously) GUI administration tool for MySQL.

When I began programming in Linux, I could not use the DB Administration tool my co-workers were using. They were using MySQL Front End - and only the windows version was available for it. Since no one in my office used Linux, none knew another DBA for Linux. One suggested Tora, but I could not get it to run - some dependencies were missing. Anyway, I was forced to use mysql - yes, the command line client that came with MySQL Server. You can start it by bringing up a terminal and entering 'mysql' into it. Then you type every SQL command you want to execute into it. Trust me, it was not fun. The biggest problems was that I want to copy something and press 'Ctrl+C' - in the terminal 'Ctrl+C' is not Copy - its Close. So the client goes down. It was very irritating. Anyway I found phpMyAdmin before long and have been using it ever since(even after I got Tora running).

The biggest advantages of phpMyAdmin is that it is web based - it runs on any server capable of handling PHP. Because of this all the online Database Administration is done using phpMyAdmin. If you have a site of your own, and you have the control panel access to it, you will know what I mean. They database management part will be handled by phpMyAdmin. The phpMyAdmin site cites the following as the features of phpMyAdmin...

  • Browse, view and drop databases, tables, views, fields and indexes.
  • Create, copy, drop, rename and alter databases, tables, fields and indexes.
  • Maintenance server, databases and tables, with proposals on server configuration.
  • Execute, edit and bookmark any SQL-statement, even batch-queries.
  • Create and read dumps of tables - in various formats like CSV, SQL, etc.
  • Export data to various formats: CSV, SQL, XML, Excel and more.
  • Administer multiple servers.
  • Manage MySQL users and privileges.
  • Check referential integrity in MyISAM tables.
  • Using Query-by-example (QBE), create complex queries automatically connecting required tables.
  • Search globally in a database or a subset of it.
  • Support InnoDB tables and foreign keys.
  • Support mysqli, the improved MySQL extension.
  • And more...

phpMyAdmin is so commonly used that there is a book - Mastering phpMyAdmin for effective MySQL Management - that aims to teach it.



MySQL - Database Management System

In this LAMP series, we have looked at Linux and Apache - now we are going to have a look at MySQL.

MySQL is a multithreaded, multi-user, SQL based Database Management System(DBMS) under GPL license. It have become the most popular open source database because of its performance, reliability and ease of use. It is said to have more than 10 million installations.

The popularity of MySQL as a web application platform is closely tied to the popularity of PHP, which is often used with MySQL and nicknamed the Dynamic Duo.

MySQL have long been my favorite DBMS because its ease of use and great support in PHP. I have used MySQL in almost all the Web applications I have created over the years. This includes PHP as well as Perl apps. Ruby on Rails, the hottest framework right now, uses MySQL.


Please note that some of the following features are only present MySQL 5.

  • Cross-platform support
  • Stored procedures
  • Triggers
  • Cursors
  • True VARCHAR support
  • Multiple independent storage engines(more about this later)
  • Transactions with the InnoDB, BDB and Cluster storage engines; savepoints with InnoDB
  • SSL support
  • Query caching
  • Sub-SELECTs (or nested SELECTs)
  • Full-text indexing and searching using MyISAM engine
  • Embedded database library
  • Full Unicode support
  • ACID compliance using the InnoDB, BDB and Cluster engines

Used In

Some of the most popular sites using MySQL are given below...


Subscribe to : Posts