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

<!-- language-all: lang-none -->

>     +-------------+---------------------+------+-----+---------+-------+
>     | 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:

<!-- language-all: lang-sql -->

    CREATE VIEW pair AS SELECT src, dst FROM iplogs GROUP BY src, dst;
Then the actual query is...

    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;

Is this the most efficient way to get the results? Will the "pair" view update every time the query is made? Will "pair" be queried once or twice?