-- auto-generated definition

create table messages

(

    id         char(36)                 not null

        primary key,

    user_id    char(36)                 not null,

    channel_id char(36)                 not null,

    text       text collate utf8mb4_bin not null,

    created_at timestamp(6)             null,

    updated_at timestamp(6)             null,

    deleted_at timestamp(6)             null,

    constraint messages_channel_id_channels_id_foreign

        foreign key (channel_id) references channels (id)

            on update cascade on delete cascade,

    constraint messages_user_id_users_id_foreign

        foreign key (user_id) references users (id)

            on update cascade on delete cascade

)

    engine = InnoDB;

create index idx_messages_channel_id_deleted_at_created_at

    on messages (channel_id, deleted_at, created_at);

create index idx_messages_created_at

    on messages (created_at);

create index idx_messages_deleted_at_created_at

    on messages (deleted_at, created_at);

create index idx_messages_deleted_at_updated_at

    on messages (deleted_at, updated_at);

EXPLAIN SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456789' ORDER BY `created_at` DESC LIMIT 1;

ANALYZE FORMAT=JSON SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456' LIMIT 1;

{

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 0.931062234,

    "table": {

      "table_name": "messages",

      "access_type": "range",

      "possible_keys": [

        "idx_messages_created_at"

      ],

      "key": "idx_messages_created_at",

      "key_length": "8",

      "used_key_parts": [

        "created_at"

      ],

      "r_loops": 1,

      "rows": 50408,

      "r_rows": 1,

      "r_table_time_ms": 0.899001226,

      "r_other_time_ms": 0.02017687,

      "filtered": 100,

      "r_filtered": 100,

      "index_condition": "messages.created_at > '2022-04-17 00:00:00.123456'"

    }

  }

}

ANALYZE FORMAT=JSON SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456789' LIMIT 1;

{

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 0.145948996,

    "table": {

      "table_name": "messages",

      "access_type": "ALL",

      "possible_keys": [

        "idx_messages_created_at"

      ],

      "r_loops": 1,

      "rows": 1770761,

      "r_rows": 40,

      "r_table_time_ms": 0.090800227,

      "r_other_time_ms": 0.040949911,

      "filtered": 100,

      "r_filtered": 2.5,

      "attached_condition": "messages.created_at > '2022-04-17 00:00:00.123456789'"

    }

  }

}

# The MariaDB configuration file

#

# The MariaDB/MySQL tools read configuration files in the following order:

# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.

# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,

# 2. "/etc/mysql/conf.d/*.cnf" to set global options.

# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.

# 4. "~/.my.cnf" to set user-specific options.

#

# If the same option is defined multiple times, the last one will apply.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#

# This group is read both by the client and the server

# use it for options that affect everything

#

[client-server]

# Port or socket location where to connect

# port = 3306

socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory

[mariadbd]

skip-host-cache

skip-name-resolve

!includedir /etc/mysql/mariadb.conf.d/

!includedir /etc/mysql/conf.d/

#

# These groups are read by MariaDB server.

# Use it for options that only the server (but not clients) should see

# this is read by the standalone daemon and embedded servers

[server]

# this is only for the mysqld standalone daemon

[mysqld]

#

# * Basic Settings

#

#user                    = mysql

pid-file                = /run/mysqld/mysqld.pid

basedir                 = /usr

datadir                 = /var/lib/mysql

tmpdir                  = /tmp

lc-messages-dir         = /usr/share/mysql

lc-messages             = en_US

skip-external-locking

# Broken reverse DNS slows down connections considerably and name resolve is

# safe to skip if there are no "host by domain name" access grants

#skip-name-resolve

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

# bind-address            = 127.0.0.1

# ufwでプライベートネットワーク内からのみ許可

#

# * Fine Tuning

#

#key_buffer_size        = 128M

#max_allowed_packet     = 1G

#thread_stack           = 192K

#thread_cache_size      = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

#myisam_recover_options = BACKUP

#max_connections        = 100

#table_cache            = 64

#

# * Logging and Replication

#

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

# Recommend only changing this at runtime for short testing periods if needed!

#general_log_file       = /var/log/mysql/mysql.log

#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald

# and when running legacy init error logging goes to syslog due to

# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf

# Enable this if you want to have error logging into a separate file

#log_error = /var/log/mysql/error.log

# Enable the slow query log to see queries with especially long duration

#slow_query_log_file    = /var/log/mysql/mariadb-slow.log

#long_query_time        = 10

#log_slow_verbosity     = query_plan,explain

#log-queries-not-using-indexes

#min_examined_row_limit = 1000

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

#       other settings you may need to change.

#server-id              = 1

#log_bin                = /var/log/mysql/mysql-bin.log

expire_logs_days        = 10

#max_binlog_size        = 100M

#

# * SSL/TLS

#

# For documentation, please read

# https://mariadb.com/kb/en/securing-connections-for-client-and-server/

#ssl-ca = /etc/mysql/cacert.pem

#ssl-cert = /etc/mysql/server-cert.pem

#ssl-key = /etc/mysql/server-key.pem

#require-secure-transport = on

#

# * Character sets

#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full

# utf8 4-byte character set. See also client.cnf

character-set-server  = utf8mb4

collation-server      = utf8mb4_general_ci

#

# * InnoDB

#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

# Most important is to give InnoDB 80 % of the system RAM for buffer use:

# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size

innodb_buffer_pool_size = 800MB

# this is only for embedded server

[embedded]

# This group is only read by MariaDB servers, not by MySQL.

# If you use the same .cnf file for MySQL and MariaDB,

# you can put MariaDB-only options here

[mariadb]

innodb_read_only_compressed=OFF # for nextcloud

# This group is only read by MariaDB-10.6 servers.

# If you use the same .cnf file for MariaDB of different versions,

# use this group for options that older servers don't understand

[mariadb-10.6]