> the actual queries I was searching 10.21.21.0 to 10.21.30.255.
That will EXCLUDE
10.21.30.26 through 10.21.30.99
because it is doing a STRING comparison:
mysql> SELECT '10.21.21.99' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.21.99' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.25' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.25' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.26' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.26' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.99' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.99' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.100' BETWEEN '10.21.21.0' AND '10.21.30.255';
+--------------------------------------------------------+
| '10.21.30.100' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+
(0 = FALSE, 1 = TRUE)
I am not going to explain the difference between "strings" and "numbers" and "octets" and how comparisons are done. That comes from a beginning programming course.
If you have only IPv4 addresses, then use INET_ATON() and INET_NTOA(). and have saddr and daddr be INT UNSIGNED (only 4 bytes). This will solve the comparison bug.
At 1500 INSERTs per second, you should consider either LOAD DATA, or "batched" INSERTs (INSERT INTO tbl (a,b,c) VALUES (1,2,3), (2,3,4), (55,66,77)). Perhaps one second's worth of data in a single INSERT statement would be optimal.
> usually within the past 24 hours, although occasionally we get asked to look back
Possibly PARTITIONing by day would be good. Then, the typical queries would look at the latest partition or two, and keep that stuff in cache.
How many days will you keep the data? Will you purge old data? (PARTITIONing helps with that.)
(I'm still a bit confused -- is it 90GB/day or is it 9GB/day? See your first posting.)