How to create index in SQL?
What is index in SQL?
Index is a system object created on columns of a table. It makes a catalog of rows in a data table and arranges them in a specific order. So with indexes, an operation like SELECT, UPDATE, DELETE statement can locate and process specific rows much faster thus improve the query performance.
An Index is also a table with its table structure. A table can have one or multiple indexes, and each index can be created on a single column or a combination of multiple columns in the table.
So if you are having a slow running query on a big table, the chances are, you need to see if an index is created.
CREATE INDEX Syntax
Here is the generic syntax for SQL CREATE INDEX statement:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);
The syntax might vary between database management systems so make sure to use the appropriate syntax for the database you are working on.
Example
The table salaries has 1,914,177 rows and two unique columns: emp_no and from-date.
Now create an index on two unique columns:
CREATE INDEX idx_primary
ON salaries (emp_no, from_date);
Since the table is quite big with nearly 2 million rows, the indexes idx_primary will enable a much faster rows lookup and enhance the query performance tremendously.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
