Как оптимизировать WHERE IN в запросе, когда значений очень много?

Question

[Classic1988]

Доброго времени суток. Прошу помощи в решении моей проблемы.

SELECT `price`,`currency`,`status` FROM `orders` WHERE `currency` = $currency AND `date_add` >= $q[0] AND `date_add` <= $q[1] AND `user_id` IN ($ids)

Нужно получить суммы заказов за определенный период (это всегда неделя, например, от 18.01.21 00:00:00 до 24.01.21 23:59:59) в нужной валюте, нужного партнера и получить весь массив заказов. Проблема в том что у партнера много приглашенных клиентов сейчас больше 2.5 тыс (растет с каждым днём) - это и есть значение переменной $ids в WHERE IN. Заказов в неделю больше 3 миллионов. Информация по заказам хранится месяц, все что раньше - переносится в другую базу и удаляется. Сейчас в таблице 12 миллионов строк. Такой запрос выполняется очень долго. Можете подсказать как ускорить и оптимизировать этот процесс? Объединённый индекс по currency, date_add, user_id имеется

Comments

[FanatPHP]

офигеть. бессмысленный "объединённый индекс по currency, date_add, user_id" имеется, а банальный джойн аффтару неизвестен.
Как, по всей видимости, и команда EXPLAIN

Answer 1

[Антон Шаманов]

JOIN users ON users.id = orders.user_id
WHERE ... AND users.partner_id = $partner_id

Comments

[Classic1988]

SELECT `price`,`currency`,`status` FROM `orders` JOIN `referrals` ON referrals.user_id = orders.user_id WHERE `currency` = 'EUR' AND `date_add` >= '2021-01-18 00:00:00' AND `date_add` <= '2021-01-24 23:59:59' AND referrals.parent_id = 4

Работает, подскажите, пожалуйста, как на такой запрос индекс поставить?

Добавил в referrals индекс по `parent_id`, он используется. Добавил объединённый индекс в `orders` по (`currency`,`date_add`) - он не используется почему-то(

Array ( [0] => Array ( [id] => 1 [select_type] => SIMPLE [table] => referrals [partitions] => [type] => ref [possible_keys] => parent_index [key] => parent_index [key_len] => 4 [ref] => const [rows] => 898 [filtered] => 100.00 [Extra] => ) [1] => Array ( [id] => 1 [select_type] => SIMPLE [table] => orders [partitions] => [type] => ALL [possible_keys] => [key] => [key_len] => [ref] => [rows] => 15330329 [filtered] => 5.00 [Extra] => Using where; Using join buffer (hash join) ) )

[Антон Шаманов]

объединённый индекс в `orders` по (`currency`,`date_add`)

а смысл? explain в помощь

[Classic1988]

Антон Шаманов,
EXPLAIN:

Array
(
    [0] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => referrals
            [partitions] => 
            [type] => ref
            [possible_keys] => parent_index,user_id_index
            [key] => parent_index
            [key_len] => 4
            [ref] => const
            [rows] => 904
            [filtered] => 100.00
            [Extra] => 
        )

    [1] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => orders
            [partitions] => 
            [type] => ref
            [possible_keys] => user_currency_date_index
            [key] => user_currency_date_index
            [key_len] => 15
            [ref] => admin.referrals.user_id,const
            [rows] => 611
            [filtered] => 11.11
            [Extra] => Using index condition
        )

)

Число rows после индекса уменьшилось до 611 с 811000, но запрос по прежнему выполняется секунд 50. Помогите, пожалуйста. В чем может быть причина такой долгой загрузки? Думаю что выборка 3 млн записей должна выполнятся быстрее((

[Антон Шаманов]

Classic1988, штатный телепат в отпуске т.ч. хз - я понятия не имею ни о структуре таблиц, индексах, ни о железе на котором все это крутится. макс. что могу посоветовать - перенеси referrals.parent_id = 4 в конец ON или начало WHERE и добавь индексы для полей указанных в ON (после анализа explain можно удалить не используемые индексы)

[Classic1988]

Антон Шаманов,

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `details` varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `price` int unsigned NOT NULL,
  `price_difference` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=22996181 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

CREATE TABLE `referrals` (
  `id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `parent_id` int unsigned NOT NULL,
  `stream_id` int unsigned DEFAULT NULL,
  `statistic_id` int unsigned DEFAULT NULL,
  `type` tinyint(1) NOT NULL DEFAULT '0',
  `date` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1977 DEFAULT CHARSET=utf8;

Переносил referrals.parent_id и туда и туда, все равно долго выборку делает(
Индексы добавил.

Характеристики сервера:

Xeon E5-2620V3 2.4 ГГц (8 ядер)
Оперативная память: 12 ГБ
Жесткие диск: 144 ГБ
Порт: 100 Мбит/с