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) |