Differences between innodb and myisam mysql storage engines

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

Comparison between the MyISAM and InnoDB storage engines of MySQL.

innodb myisam

1

row-level locking table-level locking

2

supports transaction does not support transactions

3

foreign key constraints no foreign key constraints

4

row count is not stored internally and so slow COUNT(*)s row count is stored internally and so fast COUNT(*)s

5

automatic crash recovery no automatic crash recovery, but it does offer repair table functionality

6

stores both data and indexes in one file stores indexes in one file and data in another

7

uses a buffer pool (innodb_buffer_pool_size) to cache both data and indexes uses key buffers (key_buffer) for caching indexes and leaves the data caching management to the operating system

8

ACID(Atomicity, Consistency, Isolation and Durability) compliant not ACID compliant

Import csv file to a mysql table

Using the LOAD DATA INFILE SQL statement we can import data to a mysql table. Suppose I have a table named from_csv in the database test_csv.

Using the LOAD DATA INFILE SQL statement we can import data to a mysql table.

Suppose I have a table named from_csv in the database test_csv.

And I have csv file with following content

The following query will import this csv file to mysql table from_csv

After the execution of the query the contents of the table: is:

mysql queries on command line

In this article I like to share some tips which I am using while dealing with mysql databases.

‘-e’ is the switch which enables command line mode for ‘mysql’ client which is installed default on every Linux distros.

Some examples:

1. For listing all mysql users on the server.

2. Show values assisned to each mysql variable

Continue reading “mysql queries on command line”

Taking backup of a MySQL table and then truncating the same

One day one of my friend asked me to write a script in such a way that he need to backup the logs stored in table of a database weekly and truncate that table on a weekly basis and the logs of upto 2 months need to be saved somewhere for reference with date/week/month wise and get’s emptied at the end of 2 month and overwritten with a fresh one.

One day one of my friend asked me to write a script in such a way that he need to backup datas stored in table of a database weekly and truncate that table on a weekly basis and the datas of upto 2 months need to be saved somewhere for reference with date/week/month wise and get’s emptied at the end of 2 month and overwritten with a fresh one.

Continue reading “Taking backup of a MySQL table and then truncating the same”