Re: Inverse Lookup in Key-Value Storage: avoid disk seeks
Thanks for your reply, Rick.
My map is indeed not injective (left unique) ... means I could have
A->1
B->2
C->1
D->3
E->2
F->6
G->1
H->2
I->5
J->2
...
Now, I would like to get all the keys being mapped to "2", i.e. (B,E,H,J).
The 2's in my table are more or less evenly distributed over the database file (by order of insertion). Now unfortunately, I have millions of keys (and roughly 2000 distinct values).
Thus, even though I have an index on the VALUE column already, for each entry MySQL needs to access the disk in order to load the corresponding KEY. And my "well-distributedness" of data enforces that a lot of pages need to be touched during the inverse lookup (maybe only one KEY in one DB page). At least that is my understanding so far. So, I think, I need to have some kind of replication/redundancy, in order to keep the KEYS for equal values together. But I cannot come up with any design for that.
Edited 1 time(s). Last edit at 07/14/2011 10:33AM by Luiz Gonzales.