Подсчет совпадений разных значений в mysql

В mysql ведётся лог недоступности оборудования. Встала задача считать, сколько раз оборудование "падало" в заданном интервале. Структура таблицы такая:
+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | ip | varchar(15) | NO | | NULL | | | power_state | varchar(10) | NO | | NULL | | | power_time | int(10) | NO | | NULL | | +-------------+-------------+------+-----+---------+----------------+
На данный момент подсчитываю количество падений весьма извращенным способом (получаю список ip из другой таблицы и делаю запрос в эту) Понимаю, что можно сделать как-то по человечески, но как - не могу придумать..
$query="SELECT * FROM `device_data` WHERE `visible` = '1'"; //список ip $result = mysql_query($query) or die(mysql_error()); for ($c=0; $c '$datefrom' AND `power_time` < '$dateto'"); $num_rows = mysql_num_rows($down_query);
echo " $ip $num_rows "; }
Как результат - задача выполнена, но долго. Помогите оптимизировать, либо пните в нужную сторону.

Ответ

1. JOIN
В принципе, про джойн уже написали. Это должно быть на автомате - если запрос в цикле, то джойн. Делается он совсем несложно - берутся две таблицы и связываются по общему полю:
SELECT ip FROM power_data p, device_data d WHERE p.ip=d.ip
и вот мы уже получили сводную таблицу, в которой к первой дописаны данные из второй, подтянутые по ip=ip (причем из первой данные не берутся, если во второй нет связи. За это отвечает тип джойна). Дальше к этому запросу можно дописывать любые операторы как обычно. d и p - это алиасы, для сокращения писанины.
2. GROUP BY
Теперь GROUP BY. Это ведь совсем несложно. Сгруппировать по одинаковым IP и попросить базу посчитать количество вхождений по ним. То есть - получить ровно те данные, которые нам нужны: ip - цифра, ip - цифра...
Причем сделано это будет внутри самой БД, без необходимости гонять все данные между скриптом и базой:
SELECT p.ip, count(1) cnt FROM power_data p, device_data d WHERE p.ip=d.ip AND p.power_state=0 AND d.visible = 1 AND p.power_time > [datefrom] AND p.power_time < [dateto] GROUP BY p.ip
EXPLAIN и индексы.
Здесь без готовой базы (причем заполненной данными - это важно), получить правильную аналитику не получится. Генерить тестовые данные - настолько далеко мой альтруизм не распространяется. Но обычно оптимизация запросов - это отдельный вопрос, который автору велят задавать отдельным постом, включив все запросы, а так же вывод команды, которую надо скопировать 1-в-1 и выполнить в консоли или phpmyadmin
3. EXPLAIN [тестируемый запрос]
Но в принципе можно дать пару рекомендаций и так: Надо смотреть в колонки key - актуально задействованный ключ, и rows - количество затронутых запросом строк. Причем надо перемножить между собой все цифры из этой колонки. В колонке key должно быть хоть что-то, а в колонке rows - как можно меньшее число.
Если наугад, чисто из опыта - то в device_data для этого запроса пригодился бы индекс по ip,visible, а в power_data - по power_state,power_time
Эти первые три пункта - главное. С них должна начинаться любая оптимизация и именно они дадут принципиальный прирост производительности, пренебрегать ими нельзя.
4. Мелкие оптимизации
Идея сократить размер данных в таблице - здравая. В первую очередь она касается самого IPv4 адреса, который представляет из себя ничто иное, как обычный 4-байтовый int. И может храниться в колонке соответствующего типа. Для конвертации в mysql есть функции inet_aton() и inet_ntoa() Разумеется, никаких проблем с WHERE эти функции не создают. Ну, то есть, если самому не тупить и не писать WHERE inet_ntoa(ip) = '127.0.0.1', применяя функцию к каждой записи в таблице. Применять её надо к константе, оставляя поле как есть:
WHERE ip = inet_aton('127.0.0.1')
и индекс будет работать прекрасно.
Также можно вместо слов писать в статусы цифры, и таким образом сократить размер всей записи до 16 байт!
5. PDO
Как говорилось выше и все время говорится на всех сайтах и форумах, надо от от функций mysql переходить к использованию PDO preparеd statements. Это даст сразу ТРИ эффекта:
пропадут любые ошибки, вызванные неверными данными. SQL-инъекции окажутся на свалке истории*) (о чем чаще всего не знают) множество рутинных операций станут выполняться проще и меньшим количеством кода.
Главное правило при работе с PDO - все запросы, в которых используются какие-либо переменные, должны выполняться строго через prepare()/execute(), а вместо переменных в запрос должны вставляться пейсхолдеры:
$stmt = $pdo->prepare("SELECT inet_ntoa(p.ip) ip, count(1) cnt FROM power_data p, device_data d WHERE p.ip=d.ip AND p.power_state=0 AND d.visible = 1 AND p.power_time > ? AND p.power_time < ? GROUP BY p.ip"); $stmt->execute(array($datefrom, $dateto)); $stats = $stmt->fetchALL(PDO::FETCH_KEY_PAIR);
в результате мы получим массив вида [ip => cnt] который очень удобно потом вывести в шаблоне:
?> $cnt)

*) утверждение не совсем верно, поскольку есть случаи, в которых PDO не справляется

PS. Вот меня часто ругают за то отношение, которое я высказываю по отношению к сообществу пхп в целом, и к тем его представителям, которые отвечают на вопроса на SO.
Ну скажите, какое может быть отношение, если три ответа, из которых
один переливает из пустого в порожнее: спросив, не ip ли v4 хранится в базе (как будто в варчар(15) можно запихнуть ipv6), потом, когда читатель ждёт уж рифмы "INT"... предлагает тот же варчар, только в профиль. Плюс немасштабируемый IN. Плюс куча мелкой непринципиальной косметики, причем не видно различия между действительно важными вещами и мелочевкой. а другие два смогли-таки в джойн, но груп бай уже не осилили.
Потом в вопрос придут добрые самаритяне, исправят "несправедливо" проставленные минусы, и все останутся довольны, а на просторах сети останется еще одна бессмысленная страница.