mysql table and indexes

Partition Table in MySQL

Partition Table : Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions. Partitioning can also bring better performance, because many queries can prune (ignore) partitions that, according to the WHERE clause, won't have the requested rows, thereby reducing the amount of data to be scanned to produce a result set. E.G. If you run a query over a million of data in case of normal table it will search for the entire table while in case of partition table it will search only at the desired partition.

Partition Table was introduced in MySQL 5.1.

Range – this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980's, 1990's, and everything beyond and including the year 2000.
Hash – this partitioning mode allows a DBA to separate data based on a computed hash key that is defined on one or more table columns, with the end goal being an equal distribution of values among partitions. For example, a DBA may create a partitioned table that has ten partitions that are based on the table's primary key.
Key – a special form of Hash where MySQL guarantees even distribution of data through a system-generated hash key.
List – this partitioning mode allows a DBA to segment data based on a pre-defined list of values that the DBA specifies. For example, a DBA may create a partitioned table that contains three partitions based on the years 2004, 2005, and 2006.
Composite – this final partitioning mode allows a DBA to perform sub-partitioning where a table is initially partitioned by, for example range partitioning, but then each partition is segmented even further by another method (for example, hash).

There are a number of benefits that come with partitioning, but the two main advantages are:

Increased performance – during scan operations, the MySQL optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution. For example, a million row table may be broken up into ten different partitions in range style so that each partition contains 100,000 rows. If a query is issued that only needs data from one of the partitions, and a table scan operation is necessary, only 100,000 rows will be accessed instead of a million. Obviously, it is much quicker for MySQL to sample 100,000 rows than one million so the query will complete much sooner. The same benefit is derived should index access be possible as local partitioned indexes are created for partitioned tables. Finally, it is possible to stripe a partitioned table across different physical drives by specifying different file system/directory paths for specific partitions. This allows physical I/O contention to be reduced when multiple partitions are accessed at the same time.
Simplified data management – partitioning allows a DBA to have more control over how data is managed inside of the database. By intelligently creating partitions, a DBA can simplify how certain data operations are performed. For example, a DBA can drop specific partitions in a partitioned table while the remaining partitions remain intact (as opposed to crafting a fragmentation-producing mass delete operation for the whole table). Further, partitions are automatically maintained by MySQL so the DBA doesn't have to manually separate and maintain a horizontal partitioning scheme for a table. For example, a DBA can create a history table that holds data for customers that are partitioned across various year ranges, and have those partitioned automatically enforced by the database server with no DBA intervention being necessary.

Following is a sample SQL statement for Creating table

Create Table Statement

CREATE TABLE t3 (mobile VARCHAR(12),created_on TIMESTAMP)

PARTITION BY RANGE (UNIX_TIMESTAMP(created_on))

(PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2012-06-16 00:00:00')),

PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2012-07-01 00:00:00')),

PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2012-07-16 00:00:00')),

PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2012-08-01 00:00:00')),

PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2012-08-16 00:00:00')),

PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-01 00:00:00')),

PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-16 00:00:00')),

PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2012-10-01 00:00:00')),

PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2012-10-16 00:00:00')),

PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2012-11-01 00:00:00')),

PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2012-11-16 00:00:00')),

PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2012-12-01 00:00:00')),

PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2012-12-16 00:00:00')),

PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')),

PARTITION p14 VALUES LESS THAN MAXVALUE

);

Insert Statement

INSERT INTO t2 VALUES('919971114501','2013-10-22 10:20:05');

Drop a Partition

ALTER TABLE t3 DROP PARTITION p5;

Refer the Following URL to know more about partitioning : http://dev.mysql.com/tech-resources/articles/partitioning.html

http://www.rcramer.com/tech/sql/mysql_partition.html

What are HEAP tables in MySQL?

HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL

e.g. Create table test(num int) Engine=Memory;

Default time set in MySQL Tables during Create Table.

CREATE TABLE TEST

(

num int(10),

trndate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

);

Default time set in MySQL Tables during Create Table.

CREATE TABLE TEST

(

num int(10),

trndate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

);

How to Create Data files and index files in different diskspace in MySQL?

Create table test(i int) data directory '/home/sandip/data',index directory '/home/sandip/index';

Default time set in MySQL Tables during Create Table.

CREATE TABLE TEST

( num int(10),

trndate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

);

What are HEAP tables in MySQL?

e.g. Create table test(num int) Engine=Memory;

What are CSV tables?

Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.

e.g. Create table test (i int not null, c char(10) not null) Engine=CSV;

Insert into test values (1,'recordone'),(2,'recordtwo'),(3,'recordthree');

select * from test;

Explain federated tables.

Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

ENGINE=FEDERATED

DEFAULTCHARSET=latin1

CONNECTION='mysql://feduser@remote_host:3306/federated/test_table;

Enum Tables with SET data datatype.

CREATE TABLE India

(

city SET('Delhi','Mumbai','Kolkata','Bangalore')

);

MERGE TABLE IN MYSQL

CREATE TABLE t1 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)) ENGINE=MyISAM; mysql> CREATE TABLE t2 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)) ENGINE=MyISAM; mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); mysql> CREATE TABLE total ( -> a INT NOT NULL AUTO_INCREMENT, -> message CHAR(20), INDEX(a)) -> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;