memcache as database

Fri Jun 13 10:06:24 UTC 2008

Hi Ludovic,

Thanks for the reply.
But the problem is how would I terminate the loop as in my case there can be
some Number in A whose range does not lie between low and high in table B. 

Thanks,

Arun Singhal
Software Engineer
MPS Technologies (A Macmillan company)

Email: a.singhal at mpstechnologies.com
Web: www.mpstechnologies.com 

Mail: MPS Technologies, 3A, 4th & 5th Floors, DLF Corporate Park, Gurgaon
122 002, Haryana, India

-----Original Message-----
From: Ludovic Levesque [mailto:luddic at gmail.com] 
Sent: Friday, June 13, 2008 3:21 PM
To: Arun Singhal
Cc: Dustin Sallings; memcached at lists.danga.com; Brian Aker
Subject: Re: memcache as database

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.