MySQL Index scope

What is an index?
A database object in the schema.
Used in a database to speed up queries against a table.
Reduce disk I/O by using quick access methods to determine location data
Separate from table, but cannot exist independently, must belong to a table
Automatically maintained by the database, indexes on the table are automatically deleted when the table is deleted
The index works like a book's directory, with almost no book without a table of contents, so there are few tables without indexes.

Automatic: When you define a primary KEY or a UNIQUE constraint on a table, the database automatically creates a corresponding index.
Manual: Users can create indexes to speed up queries.

Create an index:
To create an index manually:

CREATE INDEX index_tb_student_name ON tbl_student(stu_name)

The name of the index above can be named by itself

Use index (add index after where to increase efficiency)

select * from tbl_student where stu_name=?

In short:
Indexes are indexed on fields that are frequently queried.
Use index to optimize queries (index avoids a full scan of the table)

principle:
When an index is created in a field, the database generates an index page, and when we query the data, Oracle checks the index page first, so that it can quickly find the record to find.

Note:
If the table has very few columns, do not fit the index.
When the insert,delete,update has been executed many times, index fragmentation will occur, affecting the query speed, we should reorganize the index, the method is as follows:
Drop index index_name;
CREATE INDEX index_name on table (column)
In fact, it is to delete the index and re-establish the index.

Expansion:
For a multi-column index that is created, the index is typically used whenever the query criteria uses the leftmost column.

For queries that use like, the query will not use the index if "%aaa" is used, and ' aaa% ' will use the index.

The following tables will not use the index:
1. If there is or in the condition, it will not be used even if there is a conditional index.
2, for multi-column indexes, not the first part of the use, the index will not be used.
3. The like query starts with%
4. If the column type is a string, be sure to enclose the data in quotation marks in the condition, otherwise the index will not be used. (add Yes, string must ")
5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used.

alter table account add index my_index(login,name) 

Login is the leftmost column, name is the right column

If there is a composite index in the table, the index is on more than one column, and we note that for a multi-column index that is created, the index is generally used as long as the query condition uses the leftmost column.

At this time

select * from account where name="timchen"

Since the column on the right is used, the index will not take effect at this time, how do you know?
Add explain the value of Possible_key at this time is empty.

But when we use the left column, the value of explain is not empty.

Reproduced

timchen525 's Column

MySQL Index scope