Application of mysql joint index

Welcome to the Linux community forum and interact with 2 million technical staff to enter a log table with the following structure: CREATETABLE 'weblog '('id' int (10) unsignedNOTNULLAUTO_INCREMENT, 'IP' varchar (45) NOTNULL, 'time' timestampNOTNULLDEFAULTCURRENT _ TIMESTAMPONUPDA

Welcome to the Linux community forum and interact with 2 million technical staff> enter a log TABLE with the following structure: create table 'weblog '('id' int (10) unsigned not null AUTO_INCREMENT, 'IP' varchar (45) not null, 'time' timestamp not null default CURRENT_TIMESTAMP ON UPDA

Welcome to the Linux community forum and interact with 2 million technicians>

There is a log table with the following structure:

Create table 'weblog '(

'Id' int (10) unsigned not null AUTO_INCREMENT,

'IP' varchar (45) not null,

'Time' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

'Kind' varchar (255) not null,

Primary key ('id ')

) ENGINE = MyISAM AUTO_INCREMENT = 48024 default charset = utf8;

0.11 million records

Select count (times1) as times from (SELECT count (id) as times1 FROM weblog group by ip, kind) as

It takes 1.9 seconds to execute this query, and some of them cannot stand it. Later I found that it would take seconds to add a composite index.

Alter table weblog add INDEX 'sindex '('IP', 'kind ')

Then, the query will be reduced to 0.182 seconds.

The optimization is very obvious.

However, if

Alter table weblog add INDEX 'sindex '('IP', 'kind', 'id ')

The query slows down.

Select count (times1) as times from (SELECT count (id) as times1 FROM weblog group by kind) as

It takes 1.8025 seconds for me to optimize the joint query.

Alter table weblog add INDEX 'sindex '('kind', 'id') after Union INDEX

This time changes to 0.143 seconds.

The optimization is also very obvious.

If a group by clause is similar to a group by clause, it can improve the efficiency.