CREATE INDEX statement (SQL CREATE INDEX Statement)

 

Just like if a book has an index (index), you can find the information you need faster, and the same is true for a database. If there is no index in a data table, you must first read the entire data table ( scan table) It is very inefficient to search for data slowly, so adding "appropriate" indexes to the data table will greatly speed up the query.

Why is it appropriate? Because an indexed data table requires extra resources to update when you are in INSERT, UPDATE or DELETE, it is best to only index the fields that are frequently queried, otherwise the gains outweigh the losses!

CREATE INDEX Syntax (SQL CREATE INDEX Syntax)

CREATE INDEX index_name ON table_name (column_name);

Create a multiple-column index (Multiple-Column Index)

CREATE INDEX index_name ON table_name (column_name1, column_name2...);

When is it appropriate to create a multi-column index? If you often query a data table WHERE column_name1='xxx' AND column_name2='yyy', then you can create a common index on the two fields of column_name1 and column_name2.

DROP INDEX Statement (SQL DROP INDEX Statement)

How do we remove the index? Just use DROP INDEX.

MySQL

ALTER TABLE table_name DROP INDEX index_name;

SQL Server

DROP INDEX table_name.index_name;

Oracle

DROP INDEX index_name;

MS Access

DROP INDEX index_name ON table_name;