Improve MySql query performance in large table

Question

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",
        "Referrer",
        "UserAgent",
        "CreatedDate"
      ],
      "attached_condition": "((`customily_logs`.`eventlogs`.`LogType` = 3) and (`customily_logs`.`eventlogs`.`CreatedDate` > '2020-06-01') and (`customily_logs`.`eventlogs`.`Username` = 'myuser'))"
    }
  }
}

This is MySQL 5.7.29 on a Windows Server 2012 R2 x64. The table has 177 million rows and according to MySql Workbench, it has 65.6 GiB of data and 8.8 GiB of indexes. The Windows machine is an EC2 instance on AWS with 32 cores, 128 GB of RAM, and 2 TB of EBS storage with 9000 IOPS, although it's shared with other running apps, the CPU usage is rarely over 50% and there's always around 50 GB of free RAM. These are the server settings in my.ini:

[client]

port=3307

[mysql]
no-beep

[mysqld]

port=3307

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="WIN-2BKHQL88U78.log"

slow-query-log=1

slow_query_log_file="WIN-2BKHQL88U78-slow.log"

long_query_time=10

log-error="WIN-2BKHQL88U78.err"

relay_log="WIN-2BKHQL88U78-relay"

server-id=1

report_port=3307

lower_case_table_names=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

table_open_cache=2000

tmp_table_size=3G

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=4G

key_buffer_size=8M

read_buffer_size=64K

read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size = 24G

innodb_log_file_size=48M

innodb_thread_concurrency=33

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

I tried increasing the innodb_buffer_pool_size to 32 GB but it didn't help. I also run ANALYZE which finished almost instantly, and I checked for fragmentation in the database but it seems to be really low (checked it with this). I'm hoping to reduce the query time to 1 minute or less but I don't know if it's unrealistic. Can the usage of many longtext columns affect the query time?

Thank you very much for your help!

Answer 1

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).