Получилось, что запрос с n<=4 выполняется 8 секунд, с n=4 целых 14 секунд требует больше ресурсов. А mysql не работает restart и весь сервер потом не ребутится это очень плохо. Ну и конфиг конечно подвел прочитал целую статью про оптимизацию mysql, но оттуда взять нечего, либо в mysql 8 это устарело и уже стоит по дефолту либо innodb это не нужно, кроме этого "скотского" innodb_buffer_pool_size
Everything_is_bad, а с вами никто пись**ми не меряется, меня все устраивает в жизни. Ты вообще ничего кроме "не верю" не написал. Просто люди живут и не дергаются, надо будет выучим как сейчас.
Ипатьев, есть и потяжелее, 5 секунд это не страшно, я повторюсь в сотый раз индексы есть и есть партиции. Да SHOW VARIABLES LIKE 'innodb_buffer_pool_size' равен 134217728
Антон Антон, я не знал, что должен добавлять индексы под каждый запрос, за 4 года написано тысячи запросов и прекрасно жили до этого. Мне как программисту на php не понятно это, я так понимаю всю основную работу делает вложенный select и он завершается нормально (когда отдельно). Почему я должен делать индексы под внешний select который и должен всего лишь выбрать n=4 из уже готовых данных?
Антон Антон, я человек простой, куда mysql свопает ему виднее, не я его писал, я им пользуюсь, я делал индексы по time и по phone, таблица даже разделена на партиции по кварталу. Тут вопрос почему n<=4 работает, а n=4 не работает.
и Второй
mysql> EXPLAIN SELECT * FROM (SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR) t WHERE
n<=4;
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3704394 | 33.33 | Using where |
| 2 | DERIVED | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+------------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
2 rows in set, 2 warnings (0,00 sec)
Rsa97, вот первый
mysql> EXPLAIN SELECT id, time, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY time DESC) AS n FROM sms WHERE time > NOW() - INTERVAL 6 MONTH AND time < NOW() - INTERVAL 6 HOUR;
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | sms | d243,d244,d251 | ALL | time | NULL | NULL | NULL | 7408789 | 50.00 | Using where; Using filesort |
+----+-------------+-------+----------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 2 warnings (0,00 sec)
Rsa97, explain не делал не разу. Команду таблицы могу прислать, могу хоть всю с данными.
table
CREATE TABLE `sms` (
`_id` int unsigned NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`sid` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` varchar(34) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` bigint DEFAULT NULL,
`status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`main` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`utm_source` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_medium` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`utm_inside` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`alfa` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`short` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(8,5) NOT NULL,
`ip` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT '',
PRIMARY KEY (`_id`,`time`),
KEY `time` (`time`),
KEY `vid` (`id`),
KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=20866306 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(`time`)
(PARTITION d221 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
PARTITION d222 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB,
PARTITION d223 VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
PARTITION d224 VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB,
PARTITION d231 VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB,
PARTITION d232 VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB,
PARTITION d233 VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB,
PARTITION d234 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION d241 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
PARTITION d242 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
PARTITION d243 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
PARTITION d244 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION d251 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION d252 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB,
PARTITION d253 VALUES LESS THAN ('2025-09-01') ENGINE = InnoDB,
PARTITION d254 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB,
PARTITION d261 VALUES LESS THAN ('2026-03-01') ENGINE = InnoDB,
PARTITION d262 VALUES LESS THAN ('2026-06-01') ENGINE = InnoDB,
PARTITION d263 VALUES LESS THAN ('2026-09-01') ENGINE = InnoDB,
PARTITION d264 VALUES LESS THAN ('2027-01-01') ENGINE = InnoDB,
PARTITION d271 VALUES LESS THAN ('2027-03-01') ENGINE = InnoDB,
PARTITION d272 VALUES LESS THAN ('2027-06-01') ENGINE = InnoDB,
PARTITION d273 VALUES LESS THAN ('2027-09-01') ENGINE = InnoDB,
PARTITION d274 VALUES LESS THAN ('2028-01-01') ENGINE = InnoDB,
PARTITION dmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;