And I learned a cute trick for searching hierarchies. You know how to store a tree in a table, right: you give each node an id and store the parent id of the node as well:
CREATE TABLE node ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, parent INT, payload TEXT )
So now you can find the children of node 5 easily: SELECT * FROM node WHERE parent=5. But it's hard to select all the children of node 5. That requires a tree traversal, which involves lots of database queries, which gets jugly fast.
The cute trick is to build another table containing the path of each node (".220.127.116.11." means that this is node 19 whose parent is node 12 whose parent is node 5 whose parent is node 1). Then finding node 5 and its children is as simple as:
SELECT id,path FROM paths WHERE path LIKE "%.5.%"
Suuuper sneaky! I'm really beginning to appreciate how different it is to program in SQL...