MySQL Forums :: Performance :: Inverse Lookup in Key-Value Storage: avoid disk seeks


Advanced Search

Inverse Lookup in Key-Value Storage: avoid disk seeks
Posted by: Luiz Gonzales ()
Date: July 13, 2011 07:15AM

Dear all,

I wanted to implement a very simple key-value store on top of mysql, from String to Integer, *with* somehow efficient inverse lookup capabilities.

My (naive) approach was to use a simple table with two columns (KEY, VALUE), a uniqueness contraint on KEY, an index on VALUE (and a REPLACE INTO as an update function for loading the data).

I have several millions of strings and a inverse lookup from VALUES to KEYS takes a long time (order of 100 seconds), i.e. a simple "SELECT KEY FROM TABLE WHERE VALUE=?". Since I have an index, I assume that most of the time is spent on disk seeks. And actually, I think that my data is "well distributed" over the values, so that might be one supporting explanation.

I also tried to create one table for each possible value (0 ... 1000) .. but then the loading time totally goes beyond all repair (create file, open file, close file etc..).

Do you have any suggestion on how to find a MySQL-specific solution for my problem? I really think that I am dealing with a very fundamental problem, but I could not find anything using search.

Best regards,
Luiz



Edited 2 time(s). Last edit at 07/13/2011 07:16AM by Luiz Gonzales.

Options: ReplyQuote


Subject Views Written By Posted
Inverse Lookup in Key-Value Storage: avoid disk seeks 2380 Luiz Gonzales 07/13/2011 07:15AM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 749 Rick James 07/14/2011 09:56AM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 770 Luiz Gonzales 07/14/2011 10:30AM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 773 Rick James 07/14/2011 07:24PM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 756 Luiz Gonzales 07/15/2011 06:46AM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 664 Rick James 07/15/2011 10:12AM
Re: Inverse Lookup in Key-Value Storage: avoid disk seeks 966 Luiz Gonzales 07/16/2011 01:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.