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

revered, было что-то подобное, мне помогло разбить на 2 запроса

это вот

SELECT organization_id FROM `organization_category` WHERE category_id=90

выполнить отдельно, получить id через запятую и подставить в первый

...

там баг какой-то, или особенность, такая вложенность очень сильно замедляет

Ну давайте.... какая версия mysql?

В каком типе БД? InnoDB или что?

Почему нет индексов на category_id, status, name?

Как справедливо отмечено выше, от вложенного селекта надо избавляться однозначно. Мускуль не может правильно оптимизировать такой запрос, во временных таблицах начинает тонуть.

А так?

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

Должны быть проиндексированы поля обязательно organization.id, organization_category.organization_id, желательно organization.city, organization.name и возможно, organization_category.category_id

Версия сервера: 5.5.52-MariaDB - MariaDB Server

Версия протокола: 10

Версия PHP: 5.4.16

InnoDB - тип всех таблиц

Попробовал разделить запросы и произвести настройку сервера (параметры mysql).

Сейчас стало намного быстрее, но все равно 1-5 секунды загружается страница, что очень долго.

Вот например если взять такой запрос:

SELECT organization_id FROM `organization_category` WHERE category_id=90

Он бывает выполняется 1 секунду и делает выборку = 100 000 записей из 18 000 000.

При этом индекс category_id стоит.

Ещё, что интересно. Когда выполняю такой запрос в PHPmyadmin - выдает время на выполнение 0.05 секунд примерно.

А когда выполняю в PHP - первый раз выдает 0.5-1 сек, а потом когда берет с кеша уже уже менее 0.05 сек.

В PHP считаю время так:

$start = microtime(TRUE);
$res   = mysqli_query($this->conn, $query);
$timer = microtime(TRUE) - $start;

И вот замечаю, что когда в phpmyadmin выполняю запрос: страница вроде грузиться долго, около 1 сек, а потом выдает что запрос выполнен за 0.05 сек.

рекомендую обновить хотя бы до 10,1 т.к. там ОЧЕНЬ много вещей сделано для innoDB в плане оптимизации.

И нагрузка на сервер какая? Может диск тупо не справляется?

Также у вас какие настройки для innodb? Вы mysqltunner запускали, что он говорит?

В PHPmyadmin может из кеша подтянулось (то, что делали в PHP)?

зачем вам 100000 записей? в PHPmyadmin автоматически бьет на страницы...

Еще можно копнуть в другом направлении , если у вас в среднем на город гораздо меньше 100к организаций, то может стоит сперва вытаскивать их, список компаний по городу.

и все равно id IN (...), медленно будет работать даже при 1к, по сравнению с некоторыми другими решениями, опять же нужно исходить из содержимого базы, желательно сперва статистические данные изучить , как например описано выше, количество организаций на город и т.п.

Это нормально. Что ж вы еще хотите? 100 тыщ записей ведь надо еще перегнать куда то. На это и уходит время. Сделайте в том же запросе (без сортировки) limit на 100 записей и время скорей всего сократится до сотых долей секунды.