Q:

Подсчет совпадений разных значений в 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<mysql_num_rows($result); $c++) {
    $f = mysql_fetch_array($result);
    $ip=$f[ip];
    $down_query=mysql_query("SELECT * FROM `power_data` 
        WHERE `ip` LIKE '$ip' AND `power_state` LIKE 'DOWN' 
        AND `power_time` > '$datefrom' AND `power_time` < '$dateto'");
    $num_rows = mysql_num_rows($down_query);

    echo "
<tr>
    <td>
        $ip
    </td>
    <td>
        $num_rows
    </td>
</tr>";
}

Как результат - задача выполнена, но долго. Помогите оптимизировать, либо пните в нужную сторону. 

A:

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] который очень удобно потом вывести в шаблоне:
?>
<? foreach ($stat as $ip => $cnt)
<tr>
    <td><?=$ip?></td>
    <td><?=$cnt?></td>
</tr>
<? endforeach ?>

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

PS. Вот меня часто ругают за то отношение, которое я высказываю по отношению к сообществу пхп в целом, и к тем его представителям, которые отвечают на вопроса на SO.
Ну скажите, какое может быть отношение, если три ответа, из которых 

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

Потом в вопрос придут добрые самаритяне, исправят "несправедливо" проставленные минусы, и все останутся довольны, а на просторах сети останется еще одна бессмысленная страница.

