Creating Database Index
A database index is similar to the book's index which helps you to find a topic faster by looking up on it. For example, if you have a table as follows: products(id,name,categoryid) and you use the query like this:
SELECT * FROM products
WHERE categoryid = 10
The database server first checks if the index exists, if yes the index is used to determine the physical location of corresponding rows. Otherwise, the database server scans the whole table to find rows which meet the query.
Database index is database physical structure that use disk space in a way similar to that of a table so when you use index your database file size is increased.
SQL99 does not defines the standard syntax for creating database index but RDBMS vendors do. Here are the syntaxes of common RDBMS vendors:
CREATE DATABASE INDEX in Oracle
CREATE [UNIQUE | BITMAP]
INDEX [{schema}.]{index_name} ON
[{schema}.]{table_name}
({{column}|{column_expression} }[ASC | DESC],...)
[{physical_parameters}];
CREATE DATABASE INDEX in MS SQL Server
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX {index_name} ON
{table_name} | {view_name}
( column [ ASC | DESC ],...) [ON filegroup]
CREATE DATABASE INDEX in MySQL
CREATE [UNIQUE|FULLTEXT|SPATIAL]
INDEX index_name
[index_type] ON tbl_name (index_col_name,...)
Read On