The Application of mysql joint index has 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;
A total of 0.11 million records 1,
select count(times1) as times from (SELECT count(id) as times1 FROM weblog group by ip,kind) as a
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 for optimization. However, if
alter table weblog add INDEX `sindex` (`ip`,`kind`,'id')
The query slows down. 2,
select count(times1) as times from (SELECT count(id) as times1 FROM weblog group by kind) as a
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 is changed to 0.143 seconds. The optimization is also very obvious. If a group by similar index is used for joint indexing, it should be able to improve the efficiency.