StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSummary of MySQL detail records matching by IP address ranges - mySQL Jedi Knight required
text
Body
copied!<p>So, I have to draw upon all the powers of the greatest mySQL minds that SO has to offer. I have to summarize detail records based on the IP address in each record. Here's the scenario:</p> <p>In short, we have consortiums that want to know: "<strong><em>Which schools within my consortium watched which videos how many times</em></strong>"? In SQL terms, it amounts to COUNTing the detail records, grouped by which IP range it might fall into.</p> <ol> <li>We have several university Consortiums - each with a handful of different schools that are members.</li> <li>Each school within a consortium uses various IP ranges to access the videos that we serve to these schools.</li> <li>The IP Ranges are specified with wild cards, so each school specifies something like '100.200.35.x, 100.201.x.x, 100.202.39.50, etc.', with the average number of ranges per school being 10 or 15. </li> <li>The raw text log files to summarize are already in a database (one row for each log entry), and has the actual IP address that accessed the video file.</li> <li>There are 100's of millions of detail records, so I fully expect this to be a long slow process that runs for a considerable period. </li> <li>PHP scripts exist that can "explode" the wildcards into the individual IPs that are represented, but I fear this will be the final answer and could take weeks to run.</li> </ol> <p>(<em>For simplicity sake, I'm only going to refer to the video filename that was accessed and COUNT the log entries for it, but in fact all the details such as start/stop/duration,etc. are there and will ultimately be part of this solution</em>.)</p> <p>With Consortium records something like this: (<em>All table designs except log details open to suggestion</em>):</p> <pre><code>| id|consortium | | 10|Ivy League | | 20|California | </code></pre> <p>And School/IP records something like this:</p> <pre><code>| id|school |consortium_id| | 101|Harvard |10 | | 102|Yale |10 | | 103|UCLA |20 | | 104|Berkeley |20 | | id|school_id|ip_range | | 1| 101 |100.200.x.x | | 2| 101 |100.201.65.x | | 3| 101 |100.202.39.50 | | 4| 101 |100.202.39.51 | | 5| 101 |100.200.x.x | | 6| 101 |100.201.65.x | | 7| 101 |100.202.39.50 | </code></pre> <p>And detail records something like this:</p> <pre><code>|session |ip_address |filename | |560554790925|100.202.390.500|history101.mp4 | |406417611526|43.22.90.5 |newsreel.mp4 | |650423700223|100.202.39.50 |history101.mp4 | |650423700223|100.202.50.12 |science101.mp4 | |513057324209|100.202.39.56 |history101.mp4 | </code></pre> <p>I like to think I'm pretty handy with mySQL, but this one is stretching it, and am hoping that there's a spectacular function or set of steps that someone might offer.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload