memcache as database

[Ludovic Levesque]

Ok, then you can take the A.numbers which have type null in ascendant
order. And just increase your counter when you have a A.Number without
value in B range.

Ludo

On Fri, Jun 13, 2008 at 12:06 PM, Arun Singhal
<a.singhal at mpstechnologies.com> wrote:
> 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.
>
>