Начал тут переделывать свою старинную поделку.
Состряпал новый, правильный с точки зрения результата, запрос:
SELECT
inet_ntoa(temp1.src_ip),
round(sum(bytes)/1048576,2),
subq.*
FROM
traffic AS temp1
INNER JOIN
(SELECT
inet_ntoa(dst_ip),
round(sum(bytes)/1048576, 2),
dst_ip
FROM
traffic
WHERE
stime BETWEEN 1430424000 AND 1433102399 AND
dst_ip BETWEEN 3232235521 AND 3232235773
GROUP BY dst_ip) as subq
ON
subq.dst_ip = temp1.src_ip AND
temp1.stime BETWEEN 1430424000 AND 1433102399
GROUP BY src_ip;
Суть в том, что присутствует таблица вида: src_ip, dst_ip, packets, bytes, stime.
Туда летит информация из коллектора. Задача взять все существующие IP адреса из интервала Х и посчитать для них исходящий/входящий трафик за определенный период.
Индексы на src_ip и stime.
В таблице всего лишь чуть больше 5к строк. Но запрос отрабатывает довольно медленно, учитывая текущий объем записей и расчетные объемы в реальности.
Вывод explain такой:
+----+-------------+------------+------+---------------+------+---------+-------------+------+------ ----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------------+------+------ ----------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 29 | Using temporary; Using filesort |
| 1 | PRIMARY | temp1 | ref | sip,stime | sip | 5 | subq.dst_ip | 146 | Using where |
| 2 | DERIVED | traffic | ALL | stime | NULL | NULL | NULL | 5287 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+-------------+------+------ ----------------------------------------+
Почему full view? Ведь поле с индексом? Там, правда, есть повторяющиеся значения... Есть мнение, что при уникальных будет сильно лучше с тем же типом индекса. Так ли это?
Или запрос можно оптимизировать каким-то образом? Но на сегодняшний день чего-то в голову не приходят более светлые идеи.
Предполагаемый объем не менее 3-5М записей, даже больше. Нужно, чтобы запрос отрабатывал за адекватное время. Ну не более 3-5 сек. хотя бы. Думаю такое время будет вполне нормальным, поскольку проект для внутреннего использования. Даже можно сказать для служебных целей.