[tor-bugs] #15844 [Onionoo]: Develop database schema to support Onionoo's search parameter efficiently

[Tor Bug Tracker & Wiki]

#15844: Develop database schema to support Onionoo's search parameter efficiently
-----------------------------+-----------------
     Reporter:  karsten      |      Owner:
         Type:  enhancement  |     Status:  new
     Priority:  normal       |  Milestone:
    Component:  Onionoo      |    Version:
   Resolution:               |   Keywords:
Actual Points:               |  Parent ID:
       Points:               |
-----------------------------+-----------------

Comment (by teor):

 How important is disk/memory space usage?
 Can you afford to denormalise and blow out the usage slightly, say one row
 per address, rather than one row per server?

 For example, you could have one table `server_address` with:
 * one row per relay or bridge address,
 * duplicated server information against each address, and
 * a column for the original fingerprint and another column for the hashed
 fingerprint

 Then you could search in the one table, but still have the advantages of
 searching separate columns by the start of a string (this works well with
 indexes).

 One assumption that I'm making here is that you're focusing on the speed
 of a single-string search. Doing an intersection is then O(n) by the
 number of terms, or perhaps better if you feed the results of the previous
 search into your next search.

 Oh, do you have a unique indexed / primary key / integer identity column
 on the table?
 That will help with creating unions and looking up rows, and other
 indexes.

--
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/15844#comment:4>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online