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

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

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 Мбит/с

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

Антон Шаманов,
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 млн записей должна выполнятся быстрее((

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

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