Share on Google+Share on Google+

Mysql Date Index

Advertisement
Mysql Date Index is used to create a index on specified table. Indexes in database are similar to books in library.

Mysql Date Index

     

Mysql Date Index is used to create a index on specified table. Indexes in database are similar to books in library. It is created on one or more combination of columns in a database table. An Index is a database structure which arranges the value of one or columns in a specific order of database table.

Understand with Example

The Tutorial grasp you an example from 'Mysql Date Index'. To understand example we create a table 'userform' with given fieldnames and datatypes respectively. The table 'userform' has a Primary Key fieldname 'ID'.  

Create table "userform":

CREATE TABLE `userform` ( 
`ID` int(11) NOT NULL auto_increment, 
`username` varchar(100) default NULL, 
`fname` varchar(100) default NULL, 
`email` varchar(100) default NULL, 
`today` date default NULL, 
PRIMARY KEY (`ID`) 

Describe the table "userform":

The Describe table_name specifies the Fieldnames, Datatype, Null, Key, Default etc of the table 'userform'.

mysql> describe userform;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | | auto_increment |
| username | varchar(100) | YES | | | |
| fname | varchar(100) | YES | | | |
| email | varchar(100) | YES | | | |
| today | date | YES | | | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

Query to insert the record in table "userform":

The Query insert into is used to add the records or rows to the table 'userform'.

insert into userform(Id,username,fname,email,date) values (1,'vineet','vineet','vineet@roseindia.net','2008-01-08')
insert into userform(Id,username,fname,email,date) values (2,'srbh','Saurabh','srbh@roseindia.net','2008-01-08')

Query to display the record of table "userform":

mysql> select * from userform;
+----+----------+---------+----------------------+------------+
| ID | username | fname | email | today |
+----+----------+---------+----------------------+------------+
| 1 | vineet | vineet | vineet@roseindia.net | 2008-01-08 |
| 2 | saurabh | Saurabh | srbh@roseindia.net | 2008-01-08 |
+----+----------+---------+----------------------+------------+
2 rows in set (0.00 sec)

Query to index the date column "today" of table "userform":

The Query is used to create an index on column today in the userform table called date_index. Although Index enhance the search performance of a table, but slow down the updates. 

CREATE INDEX date_index ON userform(today);

Query to display the index of table "userform" :

mysql> SHOW indexes FROM userform;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| userform | 0 | PRIMARY | 1 | ID | A | 2 | NULL | | | BTREE | |
| userform | 1 | date_index | 1 | today | A | 2 | NULL | | YES | BTREE | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Advertisements

Posted on: January 21, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Discuss: Mysql Date Index  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0