The article directories

  • The index profile
  • The primary key index
  • The only index
  • Normal index
  • Composite index
  • The full text indexing

The index profile

Indexes are designed to improve database query efficiency. Indexes are suitable for fields with many queries and few modifications to improve query efficiency. If you have a lot of changes, using an index can be inefficient because it takes time to maintain the index table. Index is divided into primary key index, unique index, common index, composite index, full text index. The following illustrates the difference between using an index and not using one.

  • View the amount of data in the table

    Select count(*) from table_name;

  • View the index in the table

    Show index from table name;



    As you can see, there is only one primary key index (userId). The username field is not an index field.

    Select * from user where username = 70000; select * from user where username = 70;



    You can clearly see that indexing is fast.
  • Remove the index

    Drop Index Index name on Table name;
  • By dropping the primary key index, the field is deleted

    Alter table drop primary key drop name;

The primary key index

There can only be one primary key, but there can be more than one field in a primary key. Create a primary key index when creating a table

create table test(
   id int(11),
   name varchar(25),
   primary key (id)
);
Copy the code
  • Add the primary key index to the table after the table is created

    alter table test add constraint id primary key(id);

The only index

Unique index, the value in the index field must be unique, but null values are allowed.

  • Create a unique index when creating a table
create table test(   
	id int(11),   
	name varchar(25),   
	unique index_unique_test_name (name)
);
Copy the code
  • Create a unique index after the table is created

    create unique index index_unique_test_name on test(name);
  • Change the table structure to a unique index

    alter table test add unique index index_unique_test_name (name);

Normal index

A plain index is the most basic index and is only used to speed up queries.

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_name (name)
);
Copy the code
  • Create a normal index after the table is created

    create index index_test_name on test(name);
  • Change the table structure to a normal index

    alter table test add index index_test_name (name);

Composite index

Indicates an index created on multiple fields. The index is used only when the first field is used in the query condition. Follow the leftmost prefix set when using composite indexes.

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_id_name (id,name)
);
Copy the code
  • Create the composite index after the table is created

    create index index_test_id_name on test(id,name);
  • Change the table structure to a normal index

    alter table test add index index_test_id_name (id,name);

The full text indexing

Full-text indexes, which use full-text indexes on char, vARCHar, and text fields. That is, in a bunch of text, you can find the record line that the field belongs to by a certain keyword, etc., for example, “Welcome to this article”, through this article, you can probably find the record.

  • Create full-text indexes when creating tables
create table test(   
	id int(11),   
	name varchar(25),   
	content text,
	fulltext (text)
);
Copy the code
  • Create the composite index after the table is created

    create fulltext index index_content on test(content);
  • Change the table structure to a normal index

    alter table test add fulltext index index_content (content);