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) |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
