INDEX

MySQL

Example

CREATE INDEX idx_name
ON Employees(name);

Output

The message indicating that the operation was successful doesn’t have fixed text, but should look something like this:

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Explanation

In this example, an index idx_name is created on the name column of the Employees table. Creating an index can improve the efficiency of database operations because it allows the database to find records for specific column values more quickly.

PostgreSQL

Example

CREATE TABLE customer(
   id INT PRIMARY KEY,
   name VARCHAR (50) UNIQUE NOT NULL,
   age INT NOT NULL,
   address CHAR (25),
   salary DECIMAL (18, 2)
);

CREATE INDEX index_customer_name
ON customer (name);

Output

The database system will provide a notification once the index is created successfully. In PostgreSQL, there will be no visible output in the console, similar to the output message below:

Query returned successfully with no result in 207 msec.

Explanation

The given example demonstrates the creation of an index on the name column in the customer table. The CREATE INDEX statement is used to create an index in a table, which can boost query performance by allowing the database to find and retrieve rows faster. The index_customer_name is the name of the index, and (name) specifies the column to be indexed in the customer table.

SQL Server

Example

CREATE TABLE Users(
   ID INT PRIMARY KEY,
   Name NVARCHAR(50) NOT NULL,
   Email NVARCHAR(50) NOT NULL
);
CREATE INDEX idx_Users_Name ON Users(Name);

Output

Command(s) completed successfully.

Explanation

An index named idx_Users_Name on column Name in Users table has been created. This index will expedite search queries by SQL Server where the Name column is involved.

Oracle

Example

CREATE INDEX idx_last_name
ON employees (last_name);

Output

There is no explicit output given when an index is successfully created. If an error occurred, then Oracle would return an error message such as “ORA-01408: such column list already indexed”.

Explanation

In the provided example, an index is created on the last_name column of the employees table. This index, named idx_last_name, helps speed up the database operations related to this column, such as SELECT statements and JOIN operations. The index operates by creating a data structure (based on B-tree or Bitmap indexes) which makes the data retrieval process more efficient.

SQLite

Example

CREATE TABLE Employee
(
    EmployeeId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Salary REAL NOT NULL
);
INSERT INTO Employee (EmployeeId, Name, Salary)
VALUES (1, 'John Doe', 50000), (2, 'Jane Doe', 60000);
CREATE INDEX idx_employee_name ON Employee (Name);

Output

No output is returned for these commands in SQLite.

Explanation

In this example, an Employee table is first created with employee details. Then, an index idx_employee_name is created on the Name field of the Employee table. This will allow faster searches when querying based on Name.