Re: Does partitioning make sense for this application?
Posted by:
Rick James
Date: October 27, 2012 09:42PM
> where saddr between '10.21.21.0' and '10.21.30.255' and daddr='8.8.8.8' and dport='53';
In making an INDEX for that, start with the "=" items (in either order), then the 'range' item:
INDEX(daddr, dport, saddr)
In particular,
> KEY `stime` (`stime`,`saddr`,`daddr`) USING BTREE
is useless for that WHERE clause. This is because `stime`, the first field in the index, is not even mentioned in the WHERE clause.
> separate indexes on saddr and daddr
That worked better because it could use either one. (It is unlikely to have used both.)
My suggestion should work even better.
> 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.
Use EXPLAIN to see which index (if any) it will use.
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.
9886 values for the ENUM? OK, ENUM is probably a bad idea. Still, normalizing should work. You would need extra code to discover and add any new values as they arrive. MEDIUMINT UNSIGNED would probably be safer than SMALLINT.
> Are you sure about that? I caclculate 8 bytes.
Doh! I was working from 18+6; it is 12+6. You are right.
> Good point about IPV6. I'll have to give that some thought
My like discusses it some, and provides a way to represent IPv4 or IPv6 such that "between" will work correctly.
> The only reason I included it is because it was unique and I thought I might be able to figure some way to use it.
Some day, you will need to walk through the entire table; this will give you the tool for such.
> Like say start date -> end date tied to seq between x and y would speed up searches over time ranges.
You are thinking of a lookup table? Yes it is possible. I may have even done it once or twice. Generally, I end up not liking it, but I can't put my finger on why.