memcache as database

[Ludovic Levesque]

Ok,

so basically it's like a geo_ip database.
The bottleneck is clearly the number of updates to do, so memcache
cannot help you much.

Based on the number of rows you have on table A, the best is the following:
 - to have an index on A(type IS NULL, Number)
 - to have an index on B(LowNumber, HighNumber)
 - two possibilities:
   - make one statement which updates all A rows where type is not
null, but it takes time
   - make the following loop:
     - take the first row where type is null, do one statement which
update all A rows which have the same number and type null
     - reiterate while you have some rows with type is null

The update will look like this, for A.Number = 123456.
UPDATE A SET type = (SELECT Description FROM B WHERE LowNumber <=
A.Number AND HighNumber >= A.Number) WHERE Type IS NULL AND Number =
123456;

Hope it helps

Ludovic

On Fri, Jun 13, 2008 at 11:35 AM, Arun Singhal
<a.singhal at mpstechnologies.com> wrote:
> Hi,
>
>
>
> Comments are added below.
>
>
>
> Thanks,
>
>
>
> Arun Singhal
>
>
>
>
>
> -----Original Message-----
> From: Ludovic Levesque [mailto:luddic at gmail.com]
> Sent: Friday, June 13, 2008 2:51 PM
> To: Arun Singhal
> Cc: Dustin Sallings; memcached at lists.danga.com; Brian Aker
> Subject: Re: memcache as database
>
>
>
> Hi Arun,
>
>
>
> some questions:
>
> table A column Number: is it unique or no ? Which range is used for
>
> this numbers ?
>
> It column is not unique. This column have long number representation of an
> IP address. For e.g. Ip Address = 1.0.157.34 can be converted to long number
> = 16817442
>
>
>
> I think there is no overlap in interval of Numbers in table B, right ?
>
> Yes, there is no overlap of numbers in table B.
>
>
>
> What must be the final result ?
>
> All numbers from B (from the minimum of LowNumber to maximum of
>
> MaxNumber) must be in table A ? Or no ?
>
> No, it is not required. In final result I want that if Number column value
> from table A lie in any range of table B then its Type column got update by
> description column of B table.
>
>
>
> Ludovic
>
>
>
> On Fri, Jun 13, 2008 at 10:57 AM, Arun Singhal
>
> <a.singhal at mpstechnologies.com> wrote:
>
>> Hi,
>
>>
>
>>
>
>>
>
>> Lets take an example:
>
>>
>
>>
>
>>
>
>> 1. My first table say A (this table have approximate 100 million records)
>
>>
>
>>
>
>>
>
>> Column- ID
>
>>
>
>> Column- Number
>
>>
>
>> Column-Type
>
>>
>
>> 1
>
>>
>
>> 123456
>
>>
>
>> Null
>
>>
>
>> 2
>
>>
>
>> 111111
>
>>
>
>> Null
>
>>
>
>> 3
>
>>
>
>> 222222
>
>>
>
>> Null
>
>>
>
>> ….
>
>>
>
>> ….
>
>>
>
>> ….
>
>>
>
>> 10000000
>
>>
>
>> 333333
>
>>
>
>> Null
>
>>
>
>>
>
>>
>
>>
>
>>
>
>> Now I want to check the value of 'Number' column and if it satisfy the
>
>> condition then want to update 'Type' column.
>
>>
>
>>
>
>>
>
>> 2. My second table say B (this table has approximate 4 Lakh records)
>
>>
>
>>
>
>>
>
>> Column- ID
>
>>
>
>> Column- Low_Number
>
>>
>
>> Column High_Number
>
>>
>
>> Column_Description
>
>>
>
>> 1
>
>>
>
>> 111111
>
>>
>
>> 111119
>
>>
>
>> A
>
>>
>
>> 2
>
>>
>
>> 222222
>
>>
>
>> 222230
>
>>
>
>> B
>
>>
>
>> 3
>
>>
>
>> 123450
>
>>
>
>> 1234580
>
>>
>
>> C
>
>>
>
>> ….
>
>>
>
>> ….
>
>>
>
>> ….
>
>>
>
>> ….
>
>>
>
>> 4
>
>>
>
>> 333320
>
>>
>
>> 333339
>
>>
>
>> D
>
>>
>
>>
>
>>
>
>>
>
>>
>
>> 3. following are the steps I am using inside a stored procedure.
>
>>
>
>>
>
>>
>
>> a) select Low_Number as low, High_Number as high, Description as
>> description
>
>> from B
>
>>
>
>> b) Loop start
>
>>
>
>> c) update A set Type=description where Type is null and Number between low
>
>> and high
>
>>
>
>> d) end loop
>
>>
>
>>
>
>>
>
>>
>
>>
>
>> Now, the problem is huge data in both the tables as it is taking huge time
>
>> (in days). Can anybody guide me now?
>
>>
>
>>
>
>>
>
>> Thanks,
>
>>
>
>> Arun Singhal
>
>>
>
>> ________________________________
>
>>
>
>> From: Dustin Sallings [mailto:dustin at spy.net]
>
>> Sent: Friday, June 13, 2008 2:01 PM
>
>> To: Arun Singhal
>
>> Cc: 'Brian Aker'; memcached at lists.danga.com; a.mumtaz at mpstechnologies.com
>
>> Subject: Re: memcache as database
>
>>
>
>>
>
>>
>
>>
>
>>
>
>> On Jun 13, 2008, at 1:06, Arun Singhal wrote:
>
>>
>
>> I have million of reords in a table and I want to update a column in the
>
>>
>
>> same table by checking value of another column in the same table. For e.g.
>
>>
>
>> two columns A and B. Now if the value of column A is X then update value
>> of
>
>>
>
>> column B as true else false. Now to apply this functionality on that table
>
>>
>
>> in mysql is taking huge time.
>
>>
>
>>
>
>>
>
>>             How are you attempting to do it now? I can't imagine you being
>
>> able to do that faster than with a simple SQL query.
>
>>
>
>> I want to reduce the time by using memcache.
>
>>
>
>> Can you guide me how can I do that using memcache?
>
>>
>
>>
>
>>
>
>>             This is sort of like declaring your car to be too slow and
>
>> asking someone to install a larger exhaust pipe on it because fast cars
>> have
>
>> large exhaust pipes. You may find that the larger exhaust pipe doesn't do
>
>> nearly as much to speed up your car as shifting into second gear does.
>
>>
>
>>
>
>>
>
>>             So to truly help you, we must first understand your problem.
>> My
>
>> suspicion is that you just have a bad query or too few or (or possibly too
>
>> many) indices. If I believed memcached would solve your problem, I *still*
>
>> wouldn't be able to tell you how it to apply it without knowing exactly
>> what
>
>> you're doing.