Improve MySql query performance in large table

I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in my organization so please bear with me if I'm missing something basic. The database has a single table like this one:

CREATE TABLE `eventlogs` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `LogType` int(11) NOT NULL,
  `ProductId` longtext,
  `Username` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `ClientVersion` longtext,
  `Message` longtext,
  `Referrer` longtext,
  `UserAgent` longtext,
  `CreatedDate` datetime NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_LogType` (`LogType`),
  KEY `IX_CreatedDate` (`CreatedDate`),
  KEY `IX_Username` (`Username`)
) ENGINE=InnoDB AUTO_INCREMENT=180712975 DEFAULT CHARSET=latin1;

This used to work very well but it reached a point where it's almost impossible to run any query, they take more than 15 minutes and sometimes even more! This is the typical query we run:

SELECT * FROM customily_logs.eventlogs
    WHERE CreatedDate > '2020-06-01'
      and Username = 'myuser'
      and LogType = 3

And this is the execution plan from the query:

```
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "14073888.06"
},
"table": {
"table_name": "eventlogs",
"access_type": "index_merge",
"possible_keys": [
"IX_LogType",
"IX_CreatedDate",
"IX_Username"
],
"key": "intersect(IX_Username,IX_LogType)",
"key_length": "387,4",
"rows_examined_per_scan": 15809639,
"rows_produced_per_join": 7904819,
"filtered": "50.00",
"cost_info": {
"read_cost": "12492924.16",
"eval_cost": "1580963.90",
"prefix_cost": "14073888.06",
"data_read_per_join": "3G"
},
"used_columns": [
"Id",
"LogType",
"ProductId",
"Username",
"ClientVersion",
"Message

Good news is that you need a better use of multiple-column indexes to better correspond to your query (ref: How MySQL uses indexes).

By using your constant ref values, (the WHERE criteria is =), Username and LogType before the range CreatedDate if you create the combined index this will help with the query.

ALTER TABLE eventlogs
DROP INDEX IX_Username,
ADD INDEX IX_Username_LogType_CreatedDate(Username,LogType,CreatedDate),
ALGORITHM=INPLACE,
LOCK=NONE;

I've replaced the existing index because a similar sized index with the same prefix is equivalent.

With this <1 minute queries should be possible if the data set size returned isn't huge.

longtext columns won't be having much of an effect here unless there are a lot of rows that exceed ~8k in size (i.e. stored off row - ref) and this would affect the time of the query based on number of rows returned rather than the search time (as indexes aren't longtext).

StackExchange Database Administrators Q#294408, answer score: 6