Linux SQL statement Concise tutorial---CREATE INDEX

Index helps us to quickly find the information we need from the table. For example, let's say we want to find information about how to grow green peppers in a gardening book. If the book is not indexed, then we have to start from the beginning, until we find a place to grow straight peppers. If the book has an index, we can go to the index first to find out where the green pepper information is on which page, and then go straight to that page to read. It is clear that the use of indexes is an efficient and time-saving way.

Finding data from a database table is the same principle. If a table is not indexed, the database system needs to read the entire table (the process is called a "table scan"). If an appropriate index exists, the database system can first identify what is needed in the table, and then go directly to those places to capture the data. It's going to be a lot faster.

Therefore, indexing on a table is a good thing for system efficiency. An index can cover one or more fields. The syntax for indexing is as follows:

CREATE INDEX "index_name" on "table_name" (column_name);

Now suppose we have the following table,

Customer Table

Field name	Type of information
First_Name	CHAR (50)
Last_Name	CHAR (50)
Address	CHAR (50)
City	CHAR (50)
Country	CHAR (25)
Birth_date	Datetime

If we were to build an index on this linked fields bit of last_name, we would have to enter the following instructions,

CREATE INDEX Idx_customer_last_name
On Customer (last_name);

If we were to build an index on the Last_Name field, we would enter the following instructions,

CREATE INDEX idx_customer_location
On the Customer (city, country);

The name of the index does not have a fixed way. The usual way to do this is to add a header to the name, such as "idx_", to avoid confusion with other objects in the database. In addition, it is a good way to include the name of the table and the field name within the index name.

Please note that each database will have its own CREATE INDEX syntax, and the syntax of different repositories will be different. Therefore, before following the instruction, please confirm the correct syntax by using the database in the user manual.

Linux is measured as follows:

Reprint please specify: Xiao Liu

Linux SQL statement Concise tutorial---CREATE INDEX