Using view for repeated subquery on a large table

Question

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;

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?

Answer 1

Since your primary key contains both src and dst you might be able to get away with something like the following:

SELECT IF (src<dst,src,dst), COUNT(*)
FROM   iplogs
GROUP BY 1
UNION
SELECT IF (src<dst,dst,src), COUNT(*)
FROM iplogs
GROUP by 1

An explain suggests the query above can use the primary index (assuming the captured field is not first or that a suitable expression using the captured date is added to the where clause if it is first).

#id, select_type, table,  type,  possible_keys, key,     key_len, ref, rows, Extra
1,   PRIMARY,     iplogs, index, PRIMARY,       PRIMARY, 105,        , 6,    Using index; Using temporary; Using filesort
2,   UNION,       iplogs, index, PRIMARY,       PRIMARY, 105,        , 6,    Using index; Using temporary; Using filesort
 ,   UNION RESULT, <union1,2>,   ALL, , , , , , Using temporary

If you put EXPLAIN in front of your query you'll get details on how it will execute.

Getting down to two index scans should be as efficient as you can get and a bit simpler than using a view.

However, beware, I've only done rudimentary testing on a small data set at this point.

EDIT: With more details provided I've been able to test this and provide an improved response.

The union query given below returns two distinct portions of results. Adding these two together appears to provide the proper total. Here is a bit of PHP/MySQL code taken from some testing efforts against the supplied data.

$sql  = "SELECT IF (src<dst,src,dst) src\n";
$sql .= "      ,COUNT(DISTINCT(IF (src<dst,concat(src,'|',dst),concat(dst,'|',src)))) answer\n";
$sql .= "      ,COUNT(*) raw\n";
$sql .= "FROM   iplogs\n";
$sql .= "GROUP BY 1\n";
$sql .= "UNION\n";
$sql .= "SELECT IF (src<dst,dst,src)\n";
$sql .= "      ,COUNT(DISTINCT(IF (src<dst,concat(src,'|',dst),concat(dst,'|',src))))\n";
$sql .= "      ,COUNT(*)\n";
$sql .= "FROM iplogs\n";
$sql .= "GROUP by 1\n";
if ( !$res = mysqli_query($link,$sql) )
  die("ERROR: Unable to query vertex counts from database!\n");

However, as you can imagine, you have to post-process the query in order to add the values together to get what should be the right answer. This could be done by loading the returned records into a vertex table as is if consuming processes group by IP and sum the response.

Alternately, here is some code that does the summing and would produce records suitable for storing in a vertex table.

$qryqty = 0;
$qrylist = array();
while ( $row = mysqli_fetch_assoc($res) )
{
  $src = $row['src'];
  if ( !isset($qrylist[$src]) )
    $qrylist[$src] = 0;
  $qrylist[$src] += $row['answer'];

  $qryqty++;
}
mysqli_free_result($res);
echo "$qryqty records loaded via query<br>\n";

So, I guess there is a trade off between SQL efficiency and having to do some extra processing to sum the results either before or after storing them.

NOTE: There are no IP translations as they were loaded as text for this effort.