SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
Single-Column Indexes
CREATE INDEX index_name
ON table_name (column_name);
Unique Indexes
Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.
CREATE UNIQUE INDEX index_name on table_name (column_name);
Composite Indexes
A composite index is an index on two or more columns of a table. Its basic syntax is as follows.
CREATE INDEX index_name on table_name (column1, column2);
------------------
CREATE INDEX index_email on employees (email);
ON table_name (column1, column2, ...);
DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name;
alter table employees add index index_email(email,)
alter table employees drop index index_email;
------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| book |
| customers |
| employees |
| library |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
12 rows in set (0.10 sec)
mysql> ddesc accounts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ddesc accounts' at line 1
mysql> desc accounts;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| balance | decimal(10,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| extension | varchar(10) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| officeCode | varchar(10) | NO | MUL | NULL | |
| reportsTo | int | YES | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> create index e-index on employees (email);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-index on employees (email)' at line 1
mysql> create INDEX e-index on employees (email);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-index on employees (email)' at line 1
mysql> CREATE INDEX index_email on employees (email);
Query OK, 0 rows affected (1.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index index_email;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table employees drop index index_email;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index email_index on employees (email);
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select email from employees limit 30;
+---------------------------------+
| email |
+---------------------------------+
| abow@classicmodelcars.com |
| afixter@classicmodelcars.com |
| bjones@classicmodelcars.com |
| dmurphy@classicmodelcars.com |
| ftseng@classicmodelcars.com |
| gbondur@classicmodelcars.com |
| ghernande@classicmodelcars.com |
| gvanauf@classicmodelcars.com |
| jfirrelli@classicmodelcars.com |
| jfirrelli@classicmodelcars.com |
| lbondur@classicmodelcars.com |
| lbott@classicmodelcars.com |
| ljennings@classicmodelcars.com |
| lthompson@classicmodelcars.com |
| mgerard@classicmodelcars.com |
| mnishi@classicmodelcars.com |
| mpatterso@classicmodelcars.com |
| pcastillo@classicmodelcars.com |
| pmarsh@classicmodelcars.com |
| spatterson@classicmodelcars.com |
| tking@classicmodelcars.com |
| wpatterson@classicmodelcars.com |
| ykato@classicmodelcars.com |
+---------------------------------+
23 rows in set (0.00 sec)
mysql> create index email_index_off on employees (email,officecode);
Query OK, 0 rows affected (1.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index email_index;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table employees drop index email_index;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 104 how to create index in sql videos in telugu