“This is the 29th day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

The index

Indexes are used to sort data to speed up search and sort operations. Primary key data is always sorted, so pressing the primary key to retrieve a particular row is always a quick and efficient operation. However, searching for values in other columns is generally inefficient. At this point we can use indexes to define indexes on one or more columns and have the DBMS keep a sorted list of its contents. Once the index is defined, the DBMS uses it in a similar way to using the index of a book. The DBMS searches the sorted index, finds a match, and retrieves the rows.

Index features:

  • Indexes improve the performance of retrieval, but reduce the performance of data addition, deletion and modification. When performing these operations,DBMSIndexes must be updated dynamically.
  • Index data can take up a lot of storage space.
  • Not all data is suitable for indexing. Data with few values, such as regions, is not as valuable as data with more possible values, such as names.
  • Indexes are used for data filtering and sorting. If you often sort data in a particular order, that data may be suitable for indexing.
  • Multiple columns can be defined in an index (for example, country + city).

Normal index

Create a normal index when the table is created.

DROP TABLE IF EXISTS student;
CREATE TABLE student
(    
  	id INT(11),
	stu_name VARCHAR(10))Copy the code

Create it directly.

CREATE INDEX stu_id ON student(id);
Copy the code

Created when the table is modified.

ALTER TABLE student ADD INDEX stu_id(id);
Copy the code

Delete an index.

DROP INDEX stu_id ON student;
Copy the code


The only index

Unique index column values must be unique, but empty values are allowed (unlike primary keys). If it is a composite index, the combination of column values must be unique. In fact, in many cases, the purpose of creating unique indexes is not to speed up access, but to avoid duplication of data.

CREATE UNIQUE INDEX stu_id ON student(id);
Copy the code


Global indexes

Full-text indexes work only on CHAR, VARCHAR, TEXT, and type fields. Creating full-text indexes requires constraints with the FULLTEXT parameter.

CREATE FULLTEXT INDEX s_name ON student(stu_name);
Copy the code


The column index more

Multi-column indexes, which create indexes on multiple fields of a data table.

CREATE TABLE student
(    
  id INT(11),
	stu_name VARCHAR(10),
	email VARCHAR(20),
	INDEX info(stu_name, email)
);
Copy the code

In multi-column indexes, the index is used only if the first of these fields is used in the query condition (the STU_NAME field in the example above) (left-most prefix ‘principle). If the first field is not used, the index does nothing.

-- Use index
SELECT * FROM student WHERE stu_name = 'Joe';
SELECT * FROM student WHERE stu_name = 'bill' AND email = '[email protected]';
-- No index is used
SELECT * FROM student WHERE email = '[email protected]';
Copy the code

For those who are new to Python or want to get started with Python, you can follow the public account “Python New Horizons” to communicate and learn with others. They are all beginners. Sometimes a simple question is stuck for a long time, but others may suddenly realize it with a little help.