Mysql Date Index

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

     

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','[email protected]','2008-01-08')
insert into userform(Id,username,fname,email,date) values (2,'srbh','Saurabh','[email protected]','2008-01-08')

Query to display the record of table "userform":

mysql> select * from userform;
+----+----------+---------+----------------------+------------+
| ID | username | fname | email | today |
+----+----------+---------+----------------------+------------+
| 1 | vineet | vineet | [email protected] | 2008-01-08 |
| 2 | saurabh | Saurabh | [email protected] | 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)