Re: Does partitioning make sense for this application?
> In making an INDEX for that, start with the "="
> items (in either order), then the 'range' item:
> INDEX(daddr, dport, saddr)
>
I'm thinking now I need to completely rethink my code. I need to be able to assemble the query based on its parameters such that the equals is always first. Maybe build an array for the query so that the elements of the query can be assembled in the best order.
>
> > I wonder if I should have separate indexes on
> > saddr and daddr and then covering indexes on saddr
> > and sport and another on daddr and dport?
> Let's see all the queries.
That's the problem. The queries will vary greatly depending on what the analyst needs to know. The most predominent queries would be for a range of src IPs and one dest IP, but they literally could be anything. Show me all connections to a specific port over a specific timerange, for example. Or show me all connections to the following destination IP over x to y timerange.
>
> But, there is a big bug lurking:
> '10.21.21.0' < '10.21.3.0'
> If you are going to do range compares on IP
> addresses, you must move to a representation that
> allows correct comparisons.
You've lost me there. What do you mean by this?
>
> 9886 values for the ENUM? OK, ENUM is probably a
> bad idea.
No, I screwed that up. I was thinking about indexing ports and gave you the number for services, which are on ports. I ran a distinct query on proto and found about 6 or so. The entire list is probably no more than 10 or so.
At this point I'm realizing I need to go back to the drawing board. The author of the software and I have been discussing this as well, and he claims his software will search 365 days in a few seconds. So I need to get more familiar with his software (which was sadly way out of date on freebsd, so I submitted a port update to get it up to current) before I move forward with this.
I appreciate your help, and you've given me some great food for thought. Also your docs are quite straightforward and very useful.
I won't consume any more of your time right now, but thank you so much for the advice you've already given me.