Simon Slavin wrote: > Strongly suspect that although R*Trees produce an elegant solution to > your problem, the fact that they're a general case tool will make them too > slow to use for something like this. > > I propose an alternative solution, though I have not tried it and do not > have time to try it (sorry). > > 1) A function which turns a numeric IP address or a block into some > standard easy-to-process representation in string form. Possibly a > pair of strings with the first string being an address the second being > something indicating the extent of the block, perhaps something like > '2001:0db8:8500:0000:0000:0000:0000:0000vffff:ffff:ff00:0000:0000:0000:0000:0000'. > > You could make it shorter by leaving out the colons but my experience is > that although this leads to less data stored on disk it doesn't speed up > processing by much. But if you have a great deal of data you might want > to do it anyway. > > 2) A comparator function (maybe a SQLite function, maybe not) which > takes two such addresses or blocks and returns a value indicating whether > they're identical, whether block A contains block or address B, or > neither. > > The closest I got to the above was when I needed a program which > intensively searched and sorted individual IPv4 addresses. I got best > results by defining a SQLite function which converted IP addresses of all > formats into 'standard format' where each byte was two hex digits. All > values stored in the database were stored in my 'standard' format. This > allowed easy collation using standard text sorting.
Thanks for the suggestion, Simon. My use case is intensive on the search side, but will incur occasional updates to the structure. No sorting necessary from an application perspective. Perhaps I am being dense, but don't see how your representation eases the burden of longest-prefix matching from within SQL queries. > Everything else turned out faster to implement in my own programming > language than it was to build as SQLite functions. Yeah, I agree that the performance of a dedicated data structure will be far better. Again, just wondering if I can stretch SQLite to solve this problem, because it would be oh-so-nice to leverage all the other stuff SQLite gives us. -- Eric A. Rubin-Smith :wq _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
