I have a large table with which I need to do some analysis. The table is a log of ips...

+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| src         | int(10) unsigned    | NO   | PRI | 0       |       |
| dst         | int(10) unsigned    | NO   | PRI | 0       |       |
| packets     | int(10) unsigned    | YES  |     | NULL    |       |
| bytes       | int(16) unsigned    | YES  |     | NULL    |       |
| capturedate | int(10) unsigned    | NO   | PRI | 0       |       |
+-------------+---------------------+------+-----+---------+-------+

Every hour the past hours ip activity is collected. The time this happens is capturedate. At that time each ip pairing (unique source to destination) is stored along with the number of packets and bytes sent from source to destination.

I've written some code for a webpage that queries the database to get a "vertex cover". It collects all the ip pairings and displays each ip and how many other unique ips it has communicated with. The result looks like

ip | verticies
123.234.12.34 | 567
234.56.78.91 | 234

So ip '123.234.12.34' has communicated with 567 other unique ips (as either source or destination). With millions of records in this table efficiency is an issue. My solution is this... First I added a view for a subquery I would reuse.

CREATE VIEW pair AS SELECT src, dst FROM iplogs GROUP BY src, dst;

SELECT INET_NTOA(ip) AS ip, SUM(verts) as verticies
FROM (SELECT src AS ip, COUNT(src) AS verts FROM pair GROUP BY src
UNION ALL
SELECT dst AS ip, COUNT(dst) AS verts FROM pair GROUP BY dst) B
GROUP BY ip;

I have a large table with which I need to do some analysis. The table is a log of IPs:

+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| src         | int(10) unsigned    | NO   | PRI | 0       |       |
| dst         | int(10) unsigned    | NO   | PRI | 0       |       |
| packets     | int(10) unsigned    | YES  |     | NULL    |       |
| bytes       | int(16) unsigned    | YES  |     | NULL    |       |
| capturedate | int(10) unsigned    | NO   | PRI | 0       |       |
+-------------+---------------------+------+-----+---------+-------+

Every hour the past hours, IP activity is collected. The time this happens is capturedate. At that time each IP pairing (unique source to destination) is stored along with the number of packets and bytes sent from source to destination.

I've written some code for a webpage that queries the database to get a "vertex cover". It collects all the IP pairings and displays each IP and how many other unique IPs it has communicated with.

ip | verticies
123.234.12.34 | 567
234.56.78.91 | 234

So, IP '123.234.12.34' has communicated with 567 other unique IPs (as either source or destination). With millions of records in this table efficiency is an issue. 

My solution is this:

First I added a view for a subquery I would reuse:

CREATE VIEW pair AS SELECT src, dst FROM iplogs GROUP BY src, dst;

SELECT INET_NTOA(ip) AS ip, SUM(verts) as verticies
FROM (SELECT src AS ip, COUNT(src) AS verts FROM pair GROUP BY src
UNION ALL
SELECT dst AS ip, COUNT(dst) AS verts FROM pair GROUP BY dst) B
GROUP BY ip;