Db query is eating all cpus

Troubleshooting
1

For feature requests, please see: Contributing Feature Proposals - Mattermost.

For troubleshooting questions, please post in the following format:

Summary
I have 4 cpu at 100% due to mysql. Mysql is full of these queries:

SELECT , (SELECT count() FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = ‘’ THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE (Id = ? OR RootId = ?) AND DeleteAt = 0

SELECT p., (SELECT COUNT() FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = ‘’ THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) AS ReplyCount FROM Posts p USE INDEX(idx_posts_channel_id_delete_at_create_at) WHERE (CreateAt < (SELECT CreateAt FROM Posts WHERE Id = ‘z71bwkmx8b858feuyp476ykx5w’) AND p.ChannelId = ‘j5rewxpjw78fxei3xq7pbgt7je’ AND DeleteAt = 0) ORDER BY p.ChannelId, DeleteAt, CreateAt DESC LIMIT 30 OFFSET 0
Steps to reproduce
I have latest version but it has begun before. I just need to start mattermost and wait that someone logins.

Expected behavior
Mysql can quickly give a reply to queries above so they do not accumulate.

Observed behavior
I see mysql eating 4 cpus due to queries above.

2

I reply to myself.
Apparently database was “corrupted” or indexes where not updated.
Using this trick: alter table Posts engine = InnoDB;
Made db fast again and recovered some disk space too.