Does mysql choose joint index or single index? Which of the following is the most effective index column? In-depth Test Discussion

Create a table first

Create table 'menu _ employee '(

'Id' int (11) not null AUTO_INCREMENT COMMENT 'auto-incrementing primary key, no practical significance ',

'Employee _ pid 'int (5) default null comment' parent node serial number, which is generally the Department serial number, but there are exceptions and no group employees ',

'Employee _ id' int (5) default null comment' employee number, Department number + employee number ',

'Employee _ name' varchar (100) DEFAULT ''' COMMENT 'employee name ',

'Action' varchar (100) default null comment 'Action event path ',

Primary key ('id '),

KEY 'X' ('employee _ id', 'employee _ pid ') USING BTREE

) ENGINE = MyISAM AUTO_INCREMENT = 38 DEFAULTCHARSET = utf8 COMMENT = 'employee table'

Add data by yourself.

OK. Start the test:

Write an SQL statement and run it.

EXPLAIN

SELECT

Employee_name

FROM

Menu_employee

Force index (x)

WHERE

Employee_id> 3

AND

Employee_pid> 20

Order

Employee_pid

Create an index, follow the golden rule, and create a joint index together with WHERE.

The result is:

Isn't it strange that the index is clearly used, but it is still Using filesort!

Okay. Then we can change the single index.

Check the result again:

Is it amazing?

Let's try again. Will it be swollen if we change the index column to employee_id,

There is no effect, or Using filesort!

Single index is more effective than compound index! You must also select the columns to be indexed!

Next, composite index

Create a table first:

Create table 'l _ insertlogs '(

'Id' int (10) not null AUTO_INCREMENT,

'Counts' int (10) default null,

'Tablesname' char (30) default null,

'Operatetime' datetime default null,

Primary key ('id '),

KEY 'X' ('counts', 'id') USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 6155 DEFAULTCHARSET = utf8

Run:

EXPLAIN

SELECT

TablesName

FROM

Rochelle insertlogs

Force index (x)

WHERE

IDbetween 100 and 500

AND

Counts> 14

Order

Counts

Another index is used to create a composite double index.

The results are not nice at all.

Try again, change it to a single one, and use the primary key as the column

Still not good

Change

That's all.

Therefore, the dual index is not necessarily effective. How to Use it depends on the actual situation, and which column of the index is also very exquisite. This is just a manifestation, and I will continue to dig deeper.