Оптимизация MYSQL DB - 5 GB. 50 000 000+ строк

my.cnf

[mysqld]

collation-server = utf8_general_ci

character-set-server = utf8

local-infile=0

innodb_file_per_table = 1

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

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 127.0.0.1

innodb-buffer-pool-size = 268435456

innodb-additional-mem-pool-size = 33554432

innodb-log-file-size = 5242880

innodb-log-buffer-size = 16777216

innodb-flush-log-at-trx-commit = 2

table-cache = 400

query-cache-size = 268435456

max-allowed-packet = 134217728

thread-cache-size = 16

query-cache-limit = 16777216

key-buffer-size = 4282384384

net-buffer-length = 67108864

read-buffer-size = 134217728

read-rnd-buffer-size = 134217728

sort-buffer-size = 67108864

tmp-table-size = 268435456

max-heap-table-size = 268435456

join-buffer-size = 268435456

myisam-sort-buffer-size = 67108864

aria-pagecache-buffer-size = 268435456

long-query-time = 1

table-open-cache = 2048

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd



[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid



#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

innodb таблиц нет.

только Myisam.

Сервер:

Процессор	4 X QEMU Virtual CPU version 2.3.0 2399.996 MHz

Оперативная память	2624 / 7822 MiB

Размер дискового пространства	39.97 GiB

Файл подкачки (swap)	0 MiB

Средняя загрузка (1, 5, 15 мин)	0.42 0.32 0.28

Продолжительность работы	3 hours 44 minutes

Количество процессов	161



Apache	2.4.6-45.el7.centos.4

MySQL	5.5.52-1.el7

PHP	5.4.16-42.el7

Perl	5.16.3-291.el7

Kernel	3.10.0-514.26.2.el7.x86_64

revered, подскажите, чем Вам не подошёл запрос, который описан в ответе Оптимизайка?

Мой совет ниже будет практически на 100% аналогичен:

Сразу оговорюсь, что с Mysql не работал достаточно давно, если не учитывать небольшие пересечения с mysql-подобным синтаксисом в SphinxQL, то наверное, лет 6-7, больше с MongoDB стыкался, ну и последние несколько месяцев часть функционала на PostgreSQL перевели на проекте.

Поэтому может я что-то не то понимаю в задаче. Но из тех запросов, что выше есть, можно сделать вид, что будет достаточно простенького объединения и нескольких индексов.

ALTER TABLE `organization_category` ADD INDEX `organization_id` (`organization_id`);

ALTER TABLE `organization_category` ADD INDEX `category_id_organization` (`category_id`);

ALTER TABLE `organization` ADD INDEX `city_organization` (`city`);

ALTER TABLE `organization` ADD INDEX `com_organization` (`id`,`status`);

Теоретически может понадобится индекс по name.

Ну и предполагается, что у вас уже есть PRIMARY-индекс на `organization`. `id`

SELECT * FROM `organization`, `organization_category` WHERE `organization_category`.`organization_id` = `organization`.`id` AND `organization_category`.`category_id`=90 AND`organization`.`city`=518 AND `organization`.`status`=1 ORDER BY `name` ASC LIMIT 0,20

Четвертый индекс непонятно нужен или нет, но если одобрённых (? `status`=1) очень много, то это может приводить к скану всей таблицы, поэтому уменьшаем количество записей в индексе, которые нам нужно просмотреть через объединённый индекс по `id` и `status`.

`id` мы уже перед этим и так уже получаем в ходе работы запроса

p.s. В Mysql не хватает хорошего аналога EXPLAIN ANALYZE из PostgreSQL, он был бы не лишним.